marcosvidolin
9/12/2017 - 5:47 PM

Detect Locked Objects

Oracle Database

This script can:

  • Detect locked objects by querying.
  • Remove sessions (RDS only)
-- Usuario comum não consegue executar essa consulta

-- This script can detect locked objects by querying v$locked_object and v$lock:
select 
  (select username from v$session where sid=a.sid) blocker,
  a.sid,
  ' is blocking ',
  (select username from v$session where sid=b.sid) blockee,
  b.sid
from 
  v$lock a, 
  v$lock b
where 
  a.block = 1
and 
  b.request > 0
and 
  a.id1 = b.id1
and 
  a.id2 = b.id2;
  
  
--- 
select
  c.owner,
  c.object_name,
  c.object_type,
  b.sid,
  b.serial#,
  b.status,
  b.osuser,
  b.machine
from
  v$locked_object a ,
  v$session b,
  dba_objects c
where
  b.sid = a.session_id
and
  a.object_id = c.object_id;


-- Remove sessions travadas (esse comando é especifico para o RDS da Amazon)
begin
   rdsadmin.rdsadmin_util.kill(
       sid    => 67, 
       serial => 55315);
end;
/