Oracle session active 和 inactive 状态 说明_数据库技术_Linux公社-Linux系统门户网站
你好，游客
Oracle session active 和 inactive 状态 说明
来源：Linux社区&
作者：tianlesoftware
&&&&&&&&&&& v$session statussession
.com/docs/cd/E11882_01/server.112/e17110/dynviews_3016.htm#REFRN30223
VARCHAR2(8)
Status of the session:
ACTIVE - Session currently executing SQL
KILLED - Session marked to be killed
CACHED - Session temporarily cached for use by Oracle*XA
SNIPED - Session inactive, waiting on the client
&&&&&&&&&&&
&&&&&&&&&&& Any session that is connected to the database and is waiting for an event that does not belong to the Idle wait class is considered as an active session.
&&&&&&&&&&& killedwindowspl/sql developerkillalter system kill session 'sid,serial#' ;
&&&&&&&&&&& (SQL)DML bug
&&&&&&&&&&& inactivecommitDB session
&&&&&&&&&&& inactive session session
kill inactive session
. inactive session
&&&&&&&&&&& inactive session
sqlnet.oraexpire_time
SQLNET.EXPIRE_TIME
&&&&&&&&&&& Use parameter SQLNET.EXPIRE_TIME to specify a the time interval, in minutes, to send a probe to verify that client/server connections are active. Setting a value greater than 0 ensures that connections are not left open indefinitely, due to an abnormal client termination. If the probe finds a terminated connection, or a connection that is no longer in use, it returns an error, causing the server process to exit. This parameter is primarily intended for the database server, which typically handles multiple connections at any one time.
&&&&&&&&&&& sqlnet.expire_time Oracle Server dead connection server process.
Limitations on using this terminated connection detection feature are:
1It is not allowed on bequeathed connections.
2Though very small, a probe packet generates additional traffic that may downgrade network performance.
3Depending on which operating system is in use, the server may need to perform additional processing to distinguish the connection probing event from other events that occur. This can also result in degraded network performance.
Minimum Value 0
Recommended Value 10
SQLNET.EXPIRE_TIME=10
2.2 profile
&&&&&&&&&&& idle_time RESOURCE_LIMIT False
RESOURCE_LIMIT
Description
Parameter type
Default value
Modifiable
ALTER SYSTEM
Range of values
true | false
&&&&&&&&&&& RESOURCE_LIMIT determines whether resource limits are enforced in database profiles.
&&&&&&&&&&& TRUE Enables the enforcement of resource limits
&&&&&&&&&&& FALSEDisables the enforcement of resource limits
&&&&&&&&&&& IDLE_TIME Specify the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit.
&&&&&&&&&&& A valid database connection that is idle will respond to the probe packet causing no action on the part of the Server , whereas the resource_limit will snipe the session when idle_time is exceeded. The 'sniped' session will get disconnected when the user(or the user process) tries to communicate with the server again.
&&&&&&&&&&&
&&&&&&&&&&& -- idle_timesession idle session idlesniped (v$session).,OSprocesssession(user process) server process server process.
What does 'SNIPED' status in v$session mean?
&&&&&&&&&&& When IDLE_TIME is set in the users' profiles or the default profile. This will kill the sessions in the database (status in v$session now becomes SNIPED) and they will eventually disconnect. It does not always clean up the Unix session (LOCAL=NO sessions).
&&&&&&&&&&& At this time all oracle resources are released but the shadow processes remains and OS resources are not released. This shadow process is still counted towards the parameters of init.ora.
&&&&&&&&&&& This process is killed and entry from v$session is released only when user again tries to do something. Another way of forcing disconnect (if your users come in via SQL*Net) is to put the file sqlnet.ora on every client machine and include the parameter "SQLNET.EXPIRE_TIME" in it to force the close of the SQL*Net session
sqlnet.expire_time
&&&&&&&&&&& sqlnet.expire_time actually works on a different principle and is used to detect dead connections as opposed to disconnecting(actually 'sniping') a session based on idle_time which the profile accomplishes.
&&&&&&&&&&& But again, as you mentioned, expire_time works globally while idle_time profile works for that user. You can use both of them to make sure that the client not only gets sniped but also gets disconnected if the user process abnormally terminates.
SQL&alter profile default limit idle_time 10;
/* Formatted on
13:06:23 (QP5 v5.163.) */
& SELECT b.MACHINE, b.PROGRAM, COUNT (*)
&&& FROM v$process a, v$session b
&& WHERE a.ADDR = b.PADDR AND b.USERNAME IS NOT NULL
GROUP BY b.MACHINE, b.PROGRAM
ORDER BY COUNT (*) DESC;
相关资讯 & & &
& (07/29/:43)
& (07/29/:55)
& (08/11/:36)
& (07/29/:28)
& (07/29/:37)
& (04/08/:32)
　　　同意评论声明
　　　发表
尊重网上道德，遵守中华人民共和国的各项有关法律法规
承担一切因您的行为而直接或间接导致的民事或刑事法律责任
本站管理人员有权保留或删除其管辖留言中的任意内容
本站有权在网站内转载或引用您的评论
参与本评论即表明您已经阅读并接受上述条款本帖子已过去太久远了，不再提供回复功能。当Oracle Session被锁死的时候_数据库技术_Linux公社-Linux系统门户网站
你好，游客
当Oracle Session被锁死的时候
来源：Linux社区&
作者：salerzhang
经常出现一个问题,的某张表突然无法更新了,无论是通过pl/sql Developer 还是通过sqlplus都无法更新,执行update语句或者执行select * from table_name for update 都一样,一直处于执行状态,无法完成,这时候我就猜想是不是session被锁死了呢?带着这个问题,我们写一条SQL语句:
--锁表查询SQL
SELECT object_name, machine, s.sid, s.serial# FROM gv$locked_object l, dba_objects o, gv$session s WHERE l.object_id　= o.object_id AND l.session_id = s.
执行结果如下:
可以看到这一条数据,也就是说,存在那么一个表被锁死了,这时候呢,我们也确认了,之前无法执行update的这张表确实是因为被锁死了所以无法完成更新操作.
如何释放呢?继续执行一条SQl 语句:
ALTER system kill session '601, 802';
可以看到601 和802是查询到SID 和Serial ,格式为:alter system kill session 'sid, serial#'; 执行完成之后:
没有了被锁死的session了,这时候再去执行我们的表更新操作,可以看到,更新已经没有问题了.
更多Oracle相关信息见 专题页面
本文永久更新链接地址：
相关资讯 & & &
& (06/21/:01)
& (06/21/:49)
& (11/11/:53)
& (12/04/:21)
& (01/01/:15)
　　　同意评论声明
　　　发表
尊重网上道德，遵守中华人民共和国的各项有关法律法规
承担一切因您的行为而直接或间接导致的民事或刑事法律责任
本站管理人员有权保留或删除其管辖留言中的任意内容
本站有权在网站内转载或引用您的评论
参与本评论即表明您已经阅读并接受上述条款PLSQL_Developer使用方法及技巧_百度文库
两大类热门资源免费畅读
续费一年阅读会员，立省24元！
PLSQL_Developer使用方法及技巧
阅读已结束，下载文档到电脑
想免费下载本文？
定制HR最喜欢的简历
下载文档到电脑，方便使用
还剩4页未读，继续阅读
定制HR最喜欢的简历
你可能喜欢