remsession.sql - displays all connected sessions set echo off; set termout on; set linesize 80; set pagesize 60; set newpage 0;
select rpad(c.name':',11)rpad(' current logons=' (to_number(b.sessions_current)),20)'cumulative logons=' rpad(substr(a.value,1,10),10)'highwater mark=' b.sessions_highwater Information from v$sysstat a, v$license b, v$database c where a.name = 'logons cumulative' ;
ttitle "dbnameDatabaseUNIX/Oracle Sessions";
set heading off; select 'Sessions on database 'substr(name,1,8) from v$database; set heading on; select substr(a.spid,1,9) pid, substr(b.sid,1,5) sid, substr(b.serial#,1,5) ser#, substr(b.machine,1,6) box, substr(b.username,1,10) username, substr(b.osuser,1,8) os_user, substr(b.PRogram,1,30) program from v$session b, v$process a where b.paddr = a.addr and type='USER' order by spid; ttitle off; set heading off; select 'To kill, enter SQLPLUS> ALTER SYSTEM KILL SESSION', '''''SID, SER#'''''';' from dual; spool off; 一旦我們找到了Oracle里所有的會話，那么下一步就是運行一個腳本來檢測所有已經被鎖定的會話。這是因為，Oracle可能無法以足夠快的速度檢測到一個不活動（dead）的會話，用以防止對數據訪問的阻斷（blockage）。你可以運行下面的腳本以定位那些管制著鎖定資源的會話。
select sess.sid, sess.serial#, lo.oracle_username, lo.os_user_name, ao.object_name, lo.locked_mode from v$locked_object lo, dba_objects ao, v$session sess where ao.object_id = lo.object_id and lo.session_id = s.sid; 一旦定位了已鎖定的會話，你就可以使用下面的腳本來自動地創建ALTER SESSION句法，用來終止你所希望終止的會話，這樣就能夠將鎖定的會話從Oracle里清除掉。
spoolrun_nuke.sql
select 'alter system kill session ''' sess.sid', 'sess.serial#';' from v$locked_object lo, dba_objects ao, v$session sess where ao.object_id = lo.object_id and lo.session_id = s.sid;
