Friday, November 11, 2011

Script to track Oracle DB locks.


-- db locks with session and serial
select b.inst_id, 'alter system kill session ''' || b.sid || ',' || b.serial# || ''';' 
from gv$locked_object a , gv$session b, dba_objects c where b.sid = a.session_id 
and a.object_id = c.object_id;


-- for node1 
select b.inst_id, 'alter system kill session ''' || b.sid || ',' || b.serial# || ''';' 
from gv$locked_object a , gv$session b, dba_objects c where b.sid = a.session_id 
and a.object_id = c.object_id and b.inst_id=1;

-- transaction 2PC problem
select 'execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(' || LOCAL_TRAN_ID || ')' from dba_2pc_pending;

-- getting sql from the db locks
select inst_id, sql_text from gv$sql where sql_id in (select sql_id from gv$locked_object a , gv$session b, 
dba_objects c where b.sid = a.session_id and a.object_id = c.object_id);


--- revised 
--- tracking sid, serial and OS pid.
select p.spid,  b.username, a.sql_text from gv$sql a, gv$session b, gv$process p
where b.sql_address = a.address AND b.paddr = p.addr
  and b.sql_hash_value= a.hash_value and b.sid = &sid and b.serial# = '&serial';


-- revised- use when necessar - results can be cluttered. 
-- getting sid and serial and sql. compare with db locks sql and analyze if killing is necessary.

set lines 190
select b.sid, b.serial#,d.sql_text
from gv$locked_object a , gv$session b, dba_objects c, 
gv$sql d where b.sid = a.session_id 
and a.object_id = c.object_id and d.sql_id=b.sql_id;


-- objects that locked up.
set lines 220
col object_name format a30
col username format a8
col machine format a9
col spid format a6
col instance format 999
col object_owner format a11
col locked_mode format a10
col logon_time format a25
SELECT b.inst_id as instance,
       b.session_id AS sid,
       s.serial#,
       NVL(b.oracle_username, '(oracle)') AS username,
       a.owner AS object_owner,
       a.object_name,
       Decode(b.locked_mode, 0, 'None',
                             1, 'Null (NULL)',
                             2, 'Row-S (SS)',
                             3, 'Row-X (SX)',
                             4, 'Share (S)',
                             5, 'S/Row-X (SSX)',
                             6, 'Exclusive (X)',
                             b.locked_mode) locked_mode,
       b.os_user_name, s.machine, s.program, p.spid, s.logon_time
FROM   dba_objects a,
       gv$locked_object b, gv$session s, gv$process  p
WHERE  a.object_id = b.object_id
and    s.sid (+)= b.session_id
and    s.inst_id (+) = b.inst_id
and    s.paddr = p.addr
and    s.inst_id = p.inst_id
ORDER BY 1, 2, 3, 4;



-- tracking lock with OS PID
set long 500000
set lines 250
SET LINESIZE 80 HEADING OFF FEEDBACK OFF
SELECT
 RPAD('USERNAME : ' || s.username, 80) ||
 RPAD('OSUSER   : ' || s.osuser, 80) ||
 RPAD('PROGRAM  : ' || s.program, 80) ||
 RPAD('SPID     : ' || p.spid, 80) ||
 RPAD('SID      : ' || s.sid, 80) ||
 RPAD('SERIAL#  : ' || s.serial#, 80) ||
 RPAD('MACHINE  : ' || s.machine, 80) ||
 RPAD('TERMINAL : ' || s.terminal, 80)||
 RPAD ('STATUS  : ' || S.STATUS, 80) ||
 RPAD('SQL TXT  : ' || q.sql_text, 3000)  
 FROM gv$session s,
      gv$process p,
      gv$sql q
 WHERE s.paddr = p.addr
 AND   p.spid  = '&PID_FROM_OS'
 AND   s.sql_address        = q.address
 AND   s.sql_hash_value = q.hash_value;

-- a user causing lock ups
set lines 220
col object_name format a30
col username format a8
col machine format a9
col spid format a6
col instance format 999
col object_owner format a11
col locked_mode format a10
col logon_time format a25
SELECT b.inst_id as instance,
       b.session_id AS sid,
       s.serial#,
       NVL(b.oracle_username, '(oracle)') AS username,
       a.owner AS object_owner,
       a.object_name,
       Decode(b.locked_mode, 0, 'None',
                             1, 'Null (NULL)',
                             2, 'Row-S (SS)',
                             3, 'Row-X (SX)',
                             4, 'Share (S)',
                             5, 'S/Row-X (SSX)',
                             6, 'Exclusive (X)',
                             b.locked_mode) locked_mode,
       b.os_user_name, s.machine, s.program, p.spid, s.logon_time
FROM   dba_objects a,
       gv$locked_object b, gv$session s, gv$process  p
WHERE  a.object_id = b.object_id
and    s.sid (+)= b.session_id
and    s.inst_id (+) = b.inst_id
and    s.paddr = p.addr
and    s.inst_id = p.inst_id
and machine name like '&machinenamehere'
and wait_class != 'Idle' ;
ORDER BY 1, 2, 3, 4;

No comments:

Post a Comment