Locking and blocking information and solution

  1. Details about specified sid
  2. Script provided by oracle
  3. Find blocking session
  4. Find kill details
  5. kill -3/9 can be run on spid
  6. Queries running from more than 5 sec
  7. Details of locking objects
  8. sql being run by blocking sid
  9. Query information against the process from unix
  10. Queries running from more than 5 sec
  1. To see the details about the specified sid
    set linesize 200
    select blocking_session, sid, serial#, wait_class, seconds_in_wait
    from  gv$session where blocking_session is not null order by blocking_session
    blocking_sessionsid    serial# wait_classseconds_in_wait
    ---------------- ---------- ---------- -------------------- --------
                 148        135      61521 idle                              64

    Note: We found that session 148 is blocking session 135 and has been for 64 seconds.

  2. Run the script provided by oracle.
  3. Simple query to find the blocking session information.
    select object_id,session_id,process,locked_mode from gv$locked_object where session_id in (&session_id);
    1. This will give easy readable output for locking objects.
      set lines 100
      set pages 500
      col "lock particulars" format a100
      (select username from gv$session where sid=a.sid) || '('||a.sid||')'||
      ' is blocking to '||
      (select username from gv$session where sid=b.sid) || '('||b.sid||')' "lock particulars"
      from gv$lock a, gv$lock b where a.block = 1 and b.request> 0 and a.id1 = b.id1 and a.id2 = b.id2
      lock particulars
      qsitbba(9) is blocking to vitvcd(7)
      qsitbba(72) is blocking to vitvcd(7)
      qsitbba(9) is blocking to  qtnsdp(72)
  4. Use below to find kill details.

    Note: need to provide the sid from above query.

    setlinesize 200
    col username format a15
    colschemaname format a15
    col program format a15
    select sid,serial#,username,status,schemaname,program,to_char(logon_time,'dd/mm/yyyy hh24:ss:mm') logon_time
    from v$session where sid in(&sid);
  5. >kill -3/9 can be run on spid provided by below query.


    set linesize 100
    column spid format a10
    column username format a10
    column program format a45
    from gv$session s
    join gv$process p on p.addr = s.paddr and p.inst_id = s.inst_id
    where  s.sid=1950 ;
  6. >To see queries running from more than 5 sec.

    Note: new feature of 11g.

    set linesize 200
    select  s.sid, serial#, s.sql_id, (sysdate-sql_exec_start)*24*60*60 secs, sql_text from v$session s, v$sqltext t
    wheres.sql_id = t.sql_id
    and sql_exec_start is not null and piece = 0
    and (sysdate-sql_exec_start)*24*60*60 > 5
  7. To find the details of locking objects.
    set lines 180
    set pages 500
    col owner format         a15
    col object_name format  a20
    col object_type format  a20
    col sid                 a8
    col serial#     format  a50
    col status   format     a 10
    col osuser   format     a10
    col machine format     a60
    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 ;
  8. To find the sql being run by blocking sid
    select s.sid, s.serial#, t.sql_fulltext,t.sql_id,s.sql_hash_value,t.hash_value from v$session s, v$sql t where s.sql_address = t.address and 
    s.sql_hash_value = t.hash_value and s.sid=1950 ;
  9. To find out the query information against the process which you take from unix.
    	hash_value in (
  10. To see the queries running from more than 5 sec
    set linesize 200
    select  s.sid, serial#, s.sql_id, (sysdate-sql_exec_start)*24*60*60 secs, sql_text from v$session s, v$sqltext t
    where s.sql_id = t.sql_id
    and sql_exec_start is not null and piece = 0
    and (sysdate-sql_exec_start)*24*60*60 > 5 ;
    sid    serial# sql_idsecssql_text
    ---------- ---------- ------------- ---------- ----------------------------------------------------------------
           492      46694 a0f74y2n959cbt         12 select  /*+  first_rows(1)  use_nl (p t)  */ path_name,path_doci

November 7, 2013 В· Sanjay В· No Comments
Posted in: 3. All Scripts

Leave a Reply

You must be logged in to post a comment.