查找 Oracle 用户锁的DLL SQL,这是Oracle DBA的常使用到的,也是我每天必用的脚本之一;运行此脚本之前请用SYS用户登录: PHP程序员站
create or replace procedure SYS.EEDBA_RPT_BLOCKING_SID IS cursor C1 is select s.username Usrname, s.osuser osuser, s.sid, s.type lock_type, decode(lr.lmode,0,'None', 2,'SS',3,'SX',4,'S',5,'SSX',6,'EX') Mode_Held, lr.type, s.process UnixID, s.machine machine, s.terminal, s.status, a.owner||'.'||a.object_name object_hold, lr.block, s.program, s.logon_time from all_objects a, v$locked_object o, v$session s, v$lock lr where (id1,id2) in ( select id1,id2 from v$lock le where le.request >; 0 ) and s.sid = lr.sid and s.sid = o.session_id PHP程序员站--PHP程序员之家 and a.object_id (+)= o.object_id order by lr.id1, lr.id2, lr.block desc ; counter number := 0 ; begin dbms_output.new_line; dbms_output.put_line('Username OSUser SID Mode Type UNIX ID '|| ' Machine Terminal Active Object Logon Application'); dbms_output.put_line('======== ====== === ==== ==== ======= '|| ' ======= ======== ====== ====== ===== ==========='); for r1 in C1 loop if r1.block >; 0 and counter >; 0 then dbms_output.put_line('.'); dbms_output.put_line('.'); end if; www phperz com if r1.block = 0 then dbms_output.put('+ '); end if; dbms_output.put(r1.Usrname); dbms_output.put(' '); dbms_output.put(r1.osuser); dbms_output.put(' '); dbms_output.put(r1.sid); dbms_output.put(' '); dbms_output.put(r1.Mode_Held); dbms_output.put(' '); dbms_output.put(r1.type); dbms_output.put(' '); dbms_output.put(r1.UnixID); dbms_output.put(' '); dbms_output.put(r1.machine); www.phperz.com dbms_output.put(' '); dbms_output.put(r1.terminal); dbms_output.put(' '); dbms_output.put(r1.status); dbms_output.put(' '); dbms_output.put(r1.object_hold); dbms_output.put(' '); dbms_output.put(r1.logon_time); dbms_output.put(' '); dbms_output.put_line(r1.program); counter := counter + 1; end loop; if counter = 0 then dbms_output.put_line('.'); dbms_output.put_line('NO Blocking found'); end if; dbms_output.put_line('.'); www phperz com dbms_output.put_line('.'); dbms_output.put_line('End of report'); end EEDBA_RPT_BLOCKING_SID ; / grant execute on SYS.EEDBA_RPT_BLOCKING_SID to system / PHP程序员站--PHP程序员之家
|