How to find out blocking locks & sessions.

There are many ways to find out blocking session in Oracle or blocking locks in Oracle.You can use following SQL query to find out Blocking locks, User ID, Concurrent Request Name and Object name in oracle applications environment.




SELECT s.inst_id,
NVL (s.username, 'Internal') "Database User",
m.SID,
s.serial#,
p.spid "DB OS Process",
m.TYPE,
DECODE (m.lmode,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
lmode, LTRIM (TO_CHAR (lmode, '990'))
) "Lock Type",
DECODE (m.request,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
request, LTRIM (TO_CHAR (m.request, '990'))
) "Lock Request",
DECODE (command,
0, 'None',
DECODE (m.id2,
0, dusr.username || '.' || SUBSTR (dobj.NAME, 1, 30),
'Rollback Segment'
)
) "Object",
s.machine "Application Server",
s.process "Apps OS process",
m.ctime,
NVL (NVL (usr.description, s.action),
'Database Session'
) "Online User,Concurrent",
NVL (fnd.responsibility_name, s.module) "Responsibility,Module",
fnd.user_form_name "Form Name",
SQL.sql_text "Statement"
FROM gv$session s,
gv$lock m,
gv$process p,
apps.fnd_form_sessions_v fnd,
apps.fnd_user usr,
gv$sqlarea SQL,
dba_users dusr,
SYS.obj$ dobj
WHERE m.id1 IN (SELECT il.id1
FROM gv$lock il
WHERE il.request <> 0)
AND m.SID = s.SID
AND s.paddr = p.addr
AND s.inst_id = p.inst_id
AND SQL.inst_id(+) = s.inst_id
AND SQL.address(+) = s.sql_address
AND SQL.hash_value(+) = s.sql_hash_value
AND s.username != 'SYS'
AND m.lmode != 4
AND fnd.audsid(+) = s.audsid
AND m.inst_id = s.inst_id
AND fnd.user_name = usr.user_name(+)
AND fnd.user_id = usr.user_id(+)
AND dobj.obj#(+) = DECODE (m.id2, 0, m.id1, 1)
AND dusr.user_id(+) = dobj.owner#
ORDER BY m.id1, m.request ASC, m.SID;


You can also schedule using a crontab entry. Use following shell script and schedule it using crontab. This sheel script will create a html file and mail it to the users.


#------------------Start of script----------------------- #


#!/bin/sh
# Check Blocking Locks
#
. /u01/oracle/ORCL/orcldb/10.2.0/ORCL1_orcl-1.env
echo "Content-Type: text/html" >> Blocking_Session.html

sqlplus -s apps/apps EOF
SELECT s.inst_id, NVL (s.username, 'Internal') "Database User", m.SID,
s.serial#, p.spid "DB OS Process", m.TYPE,
DECODE (m.lmode,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
lmode, LTRIM (TO_CHAR (lmode, '990'))
) "Lock Type",
DECODE (m.request,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
request, LTRIM (TO_CHAR (m.request, '990'))
) "Lock Request",
DECODE (command,
0, 'None',
DECODE (m.id2,
0, dusr.username || '.' || SUBSTR (dobj.NAME, 1, 30),
'Rollback Segment'
)
) "Object",
s.machine "Application Server", s.process "Apps OS process", m.ctime,
NVL (NVL (usr.description, s.action),
'Database Session'
) "Online User,Concurrent",
NVL (fnd.responsibility_name, s.module) "Responsibility,Module",
fnd.user_form_name "Form Name", SQL.sql_text "Statement"
FROM gv\$session s,
gv\$lock m,
gv\$process p,
apps.fnd_form_sessions_v fnd,
apps.fnd_user usr,
gv\$sqlarea SQL,
dba_users dusr,
SYS.obj$ dobj
WHERE m.id1 IN (SELECT il.id1
FROM gv$lock il
WHERE il.request <> 0)
AND m.SID = s.SID
AND s.paddr = p.addr
AND s.inst_id = p.inst_id
AND SQL.inst_id(+) = s.inst_id
AND SQL.address(+) = s.sql_address
AND SQL.hash_value(+) = s.sql_hash_value
AND s.username != 'SYS'
AND m.lmode != 4
AND fnd.audsid(+) = s.audsid
AND m.inst_id = s.inst_id
AND fnd.user_name = usr.user_name(+)
AND fnd.user_id = usr.user_id(+)
AND dobj.obj#(+) = DECODE (m.id2, 0, m.id1, 1)
AND dusr.user_id(+) = dobj.owner#
ORDER BY m.id1, m.request ASC, m.SID;
spool off
set markup html off spool off
EOF


cat blocking_session.html >> Blocking_Session.html
(echo "Importance: High"; echo "Subject: ORCL: Blocking Session Locks"; cat Blocking_Session.html)
| /usr/sbin/sendmail -F Oracle abc@test.com

rm Blocking_Session.html
rm blocking_session.html

#--------------------End of script----------------------- #


Use following SQL to get more detail data.



SELECT   s.inst_id, NVL (s.username, 'Internal') database_user, s.SID,
         s.event, s.p1, s.serial#, p.spid, m.TYPE,
         DECODE (m.lmode,
                 0, 'None',
                 1, 'Null',
                 2, 'Row Share',
                 3, 'Row Excl.',
                 4, 'Share',
                 5, 'S/Row Excl.',
                 6, 'Exclusive',
                 lmode, LTRIM (TO_CHAR (lmode, '990'))
                ) lock_type,
            m.TYPE
         || ' - '
         || DECODE (m.TYPE,
               'BL', 'Buffer hash table instance lock',
               'CF', ' Control file schema global enqueue lock',
               'CI', 'Cross-instance function invocation instance lock',
               'CS', 'Control file schema global enqueue lock',
               'CU', 'Cursor bind lock',
               'DF', 'Data file instance lock',
               'DL', 'Direct loader parallel index create',
               'DM', 'Mount/startup db primary/secondary instance lock',
               'DR', 'Distributed recovery process lock',
               'DX', 'Distributed transaction entry lock',
               'FI', 'SGA open-file information lock',
               'FS', 'File set lock',
               'HW', 'Space management on a specific segment lock',
               'IN', 'Instance number lock',
               'IR', 'Instance recovery serialization global enqueue lock',
               'IS', 'Instance state lock',
               'IV', 'Library cache invalidation instance lock',
               'JQ', 'Job queue lock',
               'KK', 'Thread kick lock',
               'MB', 'Master buffer hash table instance lock',
               'MM', 'Mount definition gloabal enqueue lock',
               'MR', 'Media recovery lock',
               'PF', 'Password file lock',
               'PI', 'Parallel operation lock',
               'PR', 'Process startup lock',
               'PS', 'Parallel operation lock',
               'RE', 'USE_ROW_ENQUEUE enforcement lock',
               'RT', 'Redo thread global enqueue lock',
               'RW', 'Row wait enqueue lock',
               'SC', 'System commit number instance lock',
               'SH', 'System commit high water mark enqueue lock',
               'SM', 'SMON lock',
               'SN', 'Sequence number instance lock',
               'SQ', 'Sequence number enqueue lock',
               'SS', 'Sort segment lock',
               'ST', 'Space transaction enqueue lock',
               'SV', 'Sequence number value lock',
               'TA', 'Generic enqueue lock',
               'TD', 'DDL enqueue lock',
               'TE', 'Extend-segment enqueue lock',
               'TM', 'DML enqueue lock',
               'TO', 'Temporary Table Object Enqueue',
               'TT', 'Temporary table enqueue lock',
               'TX', 'Transaction enqueue lock',
               'UL', 'User supplied lock',
               'UN', 'User name lock',
               'US', 'Undo segment DDL lock',
               'WL', 'Being-written redo log instance lock',
               'WS', 'Write-atomic-log-switch global enqueue lock',
               'TS', DECODE (m.id2,
                              0, 'Temporary segment enqueue lock (ID2=0)',
                                  'New block allocation enqueue lock (ID2=1)'
                                 ),
               'LA', 'Library cache lock instance lock (A=namespace)',
               'LB', 'Library cache lock instance lock (B=namespace)',
               'LC', 'Library cache lock instance lock (C=namespace)',
               'LD', 'Library cache lock instance lock (D=namespace)',
               'LE', 'Library cache lock instance lock (E=namespace)',
               'LF', 'Library cache lock instance lock (F=namespace)',
               'LG', 'Library cache lock instance lock (G=namespace)',
               'LH', 'Library cache lock instance lock (H=namespace)',
               'LI', 'Library cache lock instance lock (I=namespace)',
               'LJ', 'Library cache lock instance lock (J=namespace)',
               'LK', 'Library cache lock instance lock (K=namespace)',
               'LL', 'Library cache lock instance lock (L=namespace)',
               'LM', 'Library cache lock instance lock (M=namespace)',
               'LN', 'Library cache lock instance lock (N=namespace)',
               'LO', 'Library cache lock instance lock (O=namespace)',
               'LP', 'Library cache lock instance lock (P=namespace)',
               'LS', 'Log start/log switch enqueue lock',
               'PA', 'Library cache pin instance lock (A=namespace)',
               'PB', 'Library cache pin instance lock (B=namespace)',
               'PC', 'Library cache pin instance lock (C=namespace)',
               'PD', 'Library cache pin instance lock (D=namespace)',
               'PE', 'Library cache pin instance lock (E=namespace)',
               'PF', 'Library cache pin instance lock (F=namespace)',
               'PG', 'Library cache pin instance lock (G=namespace)',
               'PH', 'Library cache pin instance lock (H=namespace)',
               'PI', 'Library cache pin instance lock (I=namespace)',
               'PJ', 'Library cache pin instance lock (J=namespace)',
               'PL', 'Library cache pin instance lock (K=namespace)',
               'PK', 'Library cache pin instance lock (L=namespace)',
               'PM', 'Library cache pin instance lock (M=namespace)',
               'PN', 'Library cache pin instance lock (N=namespace)',
               'PO', 'Library cache pin instance lock (O=namespace)',
               'PP', 'Library cache pin instance lock (P=namespace)',
               'PQ', 'Library cache pin instance lock (Q=namespace)',
               'PR', 'Library cache pin instance lock (R=namespace)',
               'PS', 'Library cache pin instance lock (S=namespace)',
               'PT', 'Library cache pin instance lock (T=namespace)',
               'PU', 'Library cache pin instance lock (U=namespace)',
               'PV', 'Library cache pin instance lock (V=namespace)',
               'PW', 'Library cache pin instance lock (W=namespace)',
               'PX', 'Library cache pin instance lock (X=namespace)',
               'PY', 'Library cache pin instance lock (Y=namespace)',
               'PZ', 'Library cache pin instance lock (Z=namespace)',
               'QA', 'Row cache instance lock (A=cache)',
               'QB', 'Row cache instance lock (B=cache)',
               'QC', 'Row cache instance lock (C=cache)',
               'QD', 'Row cache instance lock (D=cache)',
               'QE', 'Row cache instance lock (E=cache)',
               'QF', 'Row cache instance lock (F=cache)',
               'QG', 'Row cache instance lock (G=cache)',
               'QH', 'Row cache instance lock (H=cache)',
               'QI', 'Row cache instance lock (I=cache)',
               'QJ', 'Row cache instance lock (J=cache)',
               'QL', 'Row cache instance lock (K=cache)',
               'QK', 'Row cache instance lock (L=cache)',
               'QM', 'Row cache instance lock (M=cache)',
               'QN', 'Row cache instance lock (N=cache)',
               'QO', 'Row cache instance lock (O=cache)',
               'QP', 'Row cache instance lock (P=cache)',
               'QQ', 'Row cache instance lock (Q=cache)',
               'QR', 'Row cache instance lock (R=cache)',
               'QS', 'Row cache instance lock (S=cache)',
               'QT', 'Row cache instance lock (T=cache)',
               'QU', 'Row cache instance lock (U=cache)',
               'QV', 'Row cache instance lock (V=cache)',
               'QW', 'Row cache instance lock (W=cache)',
               'QX', 'Row cache instance lock (X=cache)',
               'QY', 'Row cache instance lock (Y=cache)',
               'QZ', 'Row cache instance lock (Z=cache)',
               '????'
                   ) lock_type_detail,
         DECODE (m.request,
                 0, 'None',
                 1, 'Null',
                 2, 'Row Share',
                 3, 'Row Excl.',
                 4, 'Share',
                 5, 'S/Row Excl.',
                 6, 'Exclusive',
                 request, LTRIM (TO_CHAR (m.request, '990'))
                ) lock_request,
         DECODE (command,
                 0, 'BACKGROUND',
                 1, 'Create Table',
                 2, 'INSERT',
                 3, 'SELECT',
                 4, 'CREATE CLUSTER',
                 5, 'ALTER CLUSTER',
                 6, 'UPDATE',
                 7, 'DELETE',
                 8, 'DROP',
                 9, 'CREATE INDEX',
                 10, 'DROP INDEX',
                 11, 'ALTER INDEX',
                 12, 'DROP TABLE',
                 13, 'CREATE SEQUENCE',
                 14, 'ALTER SEQUENCE',
                 15, 'ALTER TABLE',
                 16, 'DROP SEQUENCE',
                 17, 'GRANT',
                 18, 'REVOKE',
                 19, 'CREATE SYNONYM',
                 20, 'DROP SYNONYM',
                 21, 'CREATE VIEW',
                 22, 'DROP VIEW',
                 23, 'VALIDATE INDEX',
                 24, 'CREATE PROCEDURE',
                 25, 'ALTER PROCEDURE',
                 26, 'LOCK TABLE',
                 27, 'NO OPERATION',
                 28, 'RENAME',
                 29, 'COMMENT',
                 30, 'AUDIT',
                 31, 'NOAUDIT',
                 32, 'CREATE EXTERNAL DATABASE',
                 33, 'DROP EXTERNAL DATABASE',
                 34, 'CREATE DATABASE',
                 35, 'ALTER DATABASE',
                 36, 'CREATE ROLLBACK SEGMENT',
                 37, 'ALTER ROLLBACK SEGMENT',
                 38, 'DROP ROLLBACK SEGMENT',
                 39, 'CREATE TABLESPACE',
                 40, 'ALTER TABLESPACE',
                 41, 'DROP TABLESPACE',
                 42, 'ALTER SESSION',
                 43, 'ALTER USER',
                 44, 'COMMIT',
                 45, 'ROLLBACK',
                 46, 'SAVEPOINT',
                 47, 'PL/SQL EXECUTE',
                 48, 'SET TRANSACTION',
                 49, 'ALTER SYSTEM SWITCH LOG',
                 50, 'EXPLAIN',
                 51, 'CREATE USER',
                 52, 'CREATE ROLE',
                 53, 'DROP USER',
                 54, 'DROP ROLE',
                 55, 'SET ROLE',
                 56, 'CREATE SCHEMA',
                 57, 'CREATE CONTROL FILE',
                 58, 'ALTER TRACING',
                 59, 'CREATE TRIGGER',
                 60, 'ALTER TRIGGER',
                 61, 'DROP TRIGGER',
                 62, 'ANALYZE TABLE',
                 63, 'ANALYZE INDEX',
                 64, 'ANALYZE CLUSTER',
                 65, 'CREATE PROFILE',
                 66, 'DROP PROFILE',
                 67, 'ALTER PROFILE',
                 68, 'DROP PROCEDURE',
                 69, 'DROP PROCEDURE',
                 70, 'ALTER RESOURCE COST',
                 71, 'CREATE SNAPSHOT LOG',
                 72, 'ALTER SNAPSHOT LOG',
                 73, 'DROP SNAPSHOT LOG',
                 74, 'CREATE SNAPSHOT',
                 75, 'ALTER SNAPSHOT',
                 76, 'DROP SNAPSHOT',
                 79, 'ALTER ROLE',
                 85, 'TRUNCATE TABLE',
                 86, 'TRUNCATE CLUSTER',
                 87, '-',
                 88, 'ALTER VIEW',
                 89, '-',
                 90, '-',
                 91, 'CREATE FUNCTION',
                 92, 'ALTER FUNCTION',
                 93, 'DROP FUNCTION',
                 94, 'CREATE PACKAGE',
                 95, 'ALTER PACKAGE',
                 96, 'DROP PACKAGE',
                 97, 'CREATE PACKAGE BODY',
                 98, 'ALTER PACKAGE BODY',
                 99, 'DROP PACKAGE BODY',
                 command || ' - ???'
                ) command,
         (CASE
             WHEN m.TYPE = 'UL'
                THEN 'None '
             ELSE DECODE (command,
                          0, 'None',
                          DECODE (m.id2,
                                  0,  dusr.username
                                   || '.'
                                   || SUBSTR (dobj.NAME, 1, 30),
                                  'Rollback Segment'
                                 )
                         )
          END
         ) OBJECT,
      s.machine, s.process, m.ctime, s.program, SQL.sql_text, s.sql_id,
         (SELECT    'select * from '
                 || owner
                 || '.'
                 || object_name
                 || ' where rowid=dbms_rowid.rowid_create( 1, '
                 || row_wait_obj#
                 || ','
                 || row_wait_file#
                 || ','
                 || row_wait_block#
                 || ','
                 || row_wait_row#
                 || ' );'
            FROM dba_objects db
           WHERE db.object_id = s.row_wait_obj# AND db.object_type = 'TABLE') row_wait
    FROM gv$session s,
         gv$lock m,
         gv$process p,
         gv$sqlarea SQL,
         dba_users dusr,
         SYS.obj$ dobj
   WHERE m.id1 IN (SELECT il.id1
                     FROM gv$lock il
                    WHERE il.request <> 0)
     AND m.SID = s.SID
     AND s.paddr = p.addr
     AND s.inst_id = p.inst_id
     AND SQL.inst_id(+) = s.inst_id
     AND SQL.address(+) = s.sql_address
     AND SQL.hash_value(+) = s.sql_hash_value
     AND m.inst_id = s.inst_id
     AND dobj.obj#(+) = DECODE (m.id2, 0, m.id1, 1)
     AND dusr.user_id(+) = dobj.owner#
ORDER BY m.id1, m.request ASC, m.SID;