查找 Oracle 用户锁的DLL SQL
查找 Oracle 用户锁的DLL SQL，这是Oracle DBA的常使用到的，也是我天天必用的脚本之一；运行此脚本之前请用SYS用户登录：
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
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;
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);
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('.');
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
/