SPID,
A.SERIAL#,
a.lockwait,
A.USERNAME,
A.OSUSER,
a.logon_time,
a.last_call_et / 3600 LAST_HOUR,
A.STATUS,
'orakill ' || sid || ' ' || spid HOST_COMMAND,
'alter system kill session ''' || A.sid || ',' || A.SERIAL# || '''' SQL_COMMAND
from v$session A, V$PROCESS B
where A.PADDR = B.ADDR
AND SID > 6
其中另一个办法是在sqlnet.ora中设置sqlnet.expire_time参数（单位分钟），使其会话在该时间不活动后退出。
SQLNET.EXPIRE_TIME
Purpose
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.
Limitations on using this terminated connection detection feature are:
It is not allowed on bequeathed connections.
Though very small, a probe packet generates additional traffic that may downgrade network performance.
Depending 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.
Default
0
Minimum Value
0
Recommended Value
10
Example
SQLNET.EXPIRE_TIME=10
最后一种办法是设置用户的profile文件中的idle_time参数。
目前数据库状态：
| Event |
Waits |
Time(s) |
Avg Wait(ms) |
% Total Call Time |
Wait Class |
| CPU time |
|
517 |
|
62.9 |
|
| db file sequential read |
112,520 |
221 |
2 |
26.9 |
User I/O |
| db file scattered read |
28,598 |
50 |
2 |
6.1 |
User I/O |
| read by other session |
24,358 |
31 |
1 |
3.7 |
User I/O |
| SQL*Net more data from client |
2,888 |
14 |
5 |
1.7 |
Network |
That's all!