查找被锁定的表，并杀死进程：
set serverout on size 100000 ; declare cursor crs_sql is select distinct ' alter system kill session ' || chr( 39 ) || s.SID || ' , ' || s.SERIAL# || chr( 39 ) kill_session, decode(t.type, ' MR ' , ' Media Recovery ' , ' RT ' , ' Redo Thread ' , ' UN ' , ' User Name ' , ' TX ' , ' Transaction ' , ' TM ' , ' DML ' , ' UL ' , ' PL/SQL User Lock ' , ' DX ' , ' Distributed Xaction ' , ' CF ' , ' Control File ' , ' IS ' , ' Instance State ' , ' FS ' , ' File Set ' , ' IR ' , ' Instance Recovery ' , ' ST ' , ' Disk Space Transaction ' , ' TS ' , ' Temp Segment ' , ' IV ' , ' Library Cache Invalida-tion ' , ' LS ' , ' Log Start or Switch ' , ' RW ' , ' Row Wait ' , ' SQ ' , ' Sequence Number ' , ' TE ' , ' Extend Table ' , ' TT ' , ' Temp Table ' , ' Unknown ' ) LockType, decode(t.lmode, 0 , ' None ' , 1 , ' Null ' , 2 , ' Row-S ' , 3 , ' Row-X ' , 4 , ' Share ' , 5 , ' S/Row-X ' , 6 , ' Exclusive ' , ' Unknown ' ) LockMode, rtrim (a.object_type) || ' ' || rtrim (a.owner) || ' . ' || a. object_name object_name , decode(t.request, 0 , ' None ' , 1 , ' Null ' , 2 , ' Row-S ' , 3 , ' Row-X ' , 4 , ' Share ' , 5 , ' S/Row-X ' , 6 , ' Exclusive ' , ' Unknown ' ) RequestMode, s.MACHINE, s.MODULE from v$lock t, all_objects a, v$session s where t.sid > 6 and t.id1 = a. object_id and t.SID = s.sid; tmp_tab crs_sql % rowtype; begin open crs_sql; begin loop dbms_output.put_line( ' ********************************************************* ' ); dbms_output.new_line(); fetch crs_sql into tmp_tab; exit when crs_sql % notfound; dbms_output.put_line( ' LOCK_TYPE : ' || tmp_tab.LockType); dbms_output.put_line( ' LOCK_MODE : ' || tmp_tab.LockMode); dbms_output.put_line( ' TABLE_NAME : ' || tmp_tab. object_name ); dbms_output.put_line( ' REQUEST_MODE : ' || tmp_tab.RequestMode); dbms_output.put_line( ' MACHINE_NAME : ' || tmp_tab.MACHINE); dbms_output.put_line( ' MODULE_NAME : ' || tmp_tab.MODULE); dbms_output.put_line( ' KILL_SESSION : ' || tmp_tab.kill_session); execute immediate tmp_tab.kill_session; dbms_output.new_line(); dbms_output.put_line( ' KILL SESSION SUCCESSFULLY ! ' ); end loop; if crs_sql % notfound then dbms_output.put_line( ' NO SESSION LOCKED ! ' ); end if ; close crs_sql; dbms_output.new_line(); dbms_output.put_line( ' ********************************************************* ' ); dbms_output.new_line(); exception when others then dbms_output.put_line( ' EXCEPTION ! ' ); dbms_output.put_line( ' EXCEPTION INFO : ' || sqlerrm); end ; end ; /
