查找 Oracle 用户锁的DLL SQL这是Oracle DBA的常使用到的也是我每天必用的脚本之一运行此脚本之前请用SYS用户登录
create or replace procedure SYSEEDBA_RPT_BLOCKING_SID IS
cursor C is
select susername Usrname
sosuser osuser
ssid
stype lock_type
decode(lrlmodeNone SSSXSSSXEX) Mode_Held
lrtype
sprocess UnixID
smachine machine
sterminal
sstatus
aowner||||aobject_name object_hold
lrblock
sprogram
slogon_time
from all_objects a v$locked_object o v$session s v$lock lr
where (idid) in (
select idid
from v$lock le
where lerequest > )
and ssid = lrsid
and ssid = osession_id
and aobject_id (+)= oobject_id
order by lrid lrid lrblock desc ;
counter number := ;
begin
dbms_outputnew_line;
dbms_outputput_line(Username OSUser SID Mode Type UNIX ID ||
Machine Terminal Active Object Logon Application);
dbms_outputput_line(======== ====== === ==== ==== ======= ||
======= ======== ====== ====== ===== ===========);
for r in C loop
if rblock > and counter > then
dbms_outputput_line();
dbms_outputput_line();
end if;
if rblock = then
dbms_outputput(+ );
end if;
dbms_outputput(rUsrname);
dbms_outputput( );
dbms_outputput(rosuser);
dbms_outputput( );
dbms_outputput(rsid);
dbms_outputput( );
dbms_outputput(rMode_Held);
dbms_outputput( );
dbms_outputput(rtype);
dbms_outputput( );
dbms_outputput(rUnixID);
dbms_outputput( );
dbms_outputput(rmachine);
dbms_outputput( );
dbms_outputput(rterminal);
dbms_outputput( );
dbms_outputput(rstatus);
dbms_outputput( );
dbms_outputput(robject_hold);
dbms_outputput( );
dbms_outputput(rlogon_time);
dbms_outputput( );
dbms_outputput_line(rprogram);
counter := counter + ;
end loop;
if counter = then
dbms_outputput_line();
dbms_outputput_line(NO Blocking found);
end if;
dbms_outputput_line();
dbms_outputput_line();
dbms_outputput_line(End of report);
end EEDBA_RPT_BLOCKING_SID ;
/
grant execute on SYSEEDBA_RPT_BLOCKING_SID to system
/
