当前位置导航:炫浪网>>网络学院>>编程开发>>Oracle教程

查找 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
  /
相关内容
赞助商链接