熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

查找 Oracle 用戶鎖的DLL SQL

2013-11-13 22:19:29  來源: Oracle 

  查找 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
  /

From:http://tw.wingwit.com/Article/program/Oracle/201311/18777.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.