col "O/SUser" FOR a10
col OraUser FOR a20
col Blocking FOR a12
col ObjLocked FOR a15
col sid FOR 99999999
col pid FOR a8
SELECT s.osuser "O/SUser", s.username "OraUser", s.sid "SID",
s.serial# "Serial", s.process "PID", s.status "Status",l.name "ObjLocked",
l.mode_held "Lock Held" FROM V$SESSION s,DBA_DML_LOCKS l,V$PROCESS p
WHERE l.session_id = s.sid AND p.addr = s.paddr;

SELECT VLO.OS_USER_NAME “OS USERNAME”, VLO.ORACLE_USERNAME “DB USER”,
VP.SPID “SPID”, AO.OWNER “OWNER”, AO.OBJECT_NAME “OBJECT LOCKED”,AO.OBJECT_TYPE,
DECODE (VLO.LOCKED_MODE,
1, ‘NO LOCK’,
2, ‘ROW SHARE’,
3, ‘ROW EXCLUSIVE’,
4, ‘SHARE’,
5, ‘SHARE ROW EXCL’,
6, ‘EXCLUSIVE’,
NULL
) “MODE OF LOCK”,
VS.STATUS “CURRENT STATUS”
FROM V$LOCKED_OBJECT VLO, ALL_OBJECTS AO, V$SESSION VS, V$PROCESS VP
WHERE VLO.OBJECT_ID = AO.OBJECT_ID
AND VS.STATUS <> ‘KILLED’
AND VLO.SESSION_ID = VS.SID
AND VS.PADDR = VP.ADDR;

SELECT * FROM dba_dml_locks WHERE name LIKE ‘%RA_CUSTOMER_TRX_ALL%’

SESSION_ID    OWNER     NAME                           MODE_HELD     MODE_REQUESTED LAST_CONVERT    BLOCKING_OTHERS                          
365           AR        RA_CUSTOMER_TRX_ALL            Row-X (SX)    None           5081            NOT Blocking                            
365           AR        RA_CUST_TRX_LINE_SALESREPS_ALL Row-X (SX)    None           5081            NOT Blocking                            
365           AR        RA_CUST_TRX_LINE_GL_DIST_ALL   Row-X (SX)    None           5081            NOT Blocking

SET term ON;
SET LINES 130;
COLUMN sid_ser format a12 heading ’session,’serial#’;
COLUMN username format a12 heading ‘os user/’db user‘;
column process format a9 heading ‘os‘process’;
COLUMN spid format a7 heading ‘trace’number‘;
column owner_object format a35 heading ‘owner.object‘;
column locked_mode format a13 heading ‘locked‘mode’;
COLUMN STATUS format a8 heading ’status’;
spool locks.lst;
 
SELECT
    substr(to_char(l.session_id)”‘,’”to_char(s.serial#),1,12) sid_ser,
    substr(l.os_user_name”‘/’”l.oracle_username,1,12) username,
    l.process,
    p.spid,
    substr(o.owner”‘.’”o.object_name,1,35) owner_object,
    decode(l.locked_mode,
             1,‘No Lock’,
             2,‘Row Share’,
             3,‘Row Exclusive’,
             4,‘Share’,
             5,‘Share Row Excl’,
             6,‘Exclusive’,NULL) locked_mode,
    substr(s.STATUS,1,8) STATUS
FROM
    v$locked_object l,
    all_objects     o,
    v$session       s,
    v$process       p
WHERE
    l.object_id = o.object_id
AND l.session_id = s.sid
AND s.paddr      = p.addr
AND s.STATUS != ‘KILLED’;
 
spool off;

session,     os user/     os        trace                                       locked                                            
serial#      db user      process   number  owner.object                        mode          status                              
———— ———— ——— ——- ———————————– ————- ——–                            
81,47000     oadv1qtc/APP           23844   APPS.JTF_FM_RAPID_M_QTBL            Row Exclusive ACTIVE                              
239,31       oadv1qtc/APP 2455      27900   APPLSYS.FND_CONCURRENT_QUEUES       Row Exclusive INACTIVE                            
498,1        oadv1qtc/APP           28452   APPLSYS.AQ$_FND_CP_GSM_IPC_AQTBL_I  Row Exclusive ACTIVE                              
512,6        oadv1qtc/APP           28484   APPLSYS.AQ$_FND_CP_GSM_OPP_AQTBL_I  Row Exclusive ACTIVE                              
498,1        oadv1qtc/APP           28452   APPLSYS.AQ$_FND_CP_GSM_IPC_AQTBL_H  Row Exclusive ACTIVE                              
577,29592    oadv1qtc/APP           23268   APPS.JTF_FM_RAPID_RS_QTBL           Row Exclusive ACTIVE                              
498,1        oadv1qtc/APP           28452   APPLSYS.AQ$_FND_CP_GSM_IPC_AQTBL_T  Row Exclusive ACTIVE                              
500,6        oadv1qtc/APP           28456   APPLSYS.AQ$_FND_CP_GSM_IPC_AQTBL_I  Row Exclusive ACTIVE                              
236,27       oadv1qtc/APP           27822   APPS.JTF_FM_RAPID_B_QTBL            Row Exclusive ACTIVE                              
557,3240     /AQADM                 26352   AQADM.AQ$_XXIBE_CUST_CON_ROLES_ST_I Row Exclusive ACTIVE                              
557,3240     /AQADM                 26352   AQADM.AQ$_XXIBE_CUST_CON_ROLES_ST_H Row Exclusive ACTIVE                              
557,3240     /AQADM                 26352   AQADM.AQ$_XXIBE_CUST_CON_ROLES_ST_T Row Exclusive ACTIVE                              
179,2063     oadv1qtc/APP 11261     4449    CMF.XXCMF_ATP_LINES_ARCHIVE         Row Exclusive INACTIVE                            
500,6        oadv1qtc/APP           28456   APPLSYS.AQ$_FND_CP_GSM_IPC_AQTBL_H  Row Exclusive ACTIVE                              
557,3240     /AQADM                 26352   AQADM.AQ$_SO_LINES_ALL11_ST_I       Row Exclusive ACTIVE                              
557,3240     /AQADM                 26352   AQADM.AQ$_SO_LINES_ALL11_ST_H       Row Exclusive ACTIVE                              
536,55348    oadv1qtc/APP           16323   APPS.JTF_FM_RAPID_RQ_QTBL           Row Exclusive ACTIVE                              
557,3240     /AQADM                 26352   AQADM.AQ$_SO_LINES_ALL11_ST_T       Row Exclusive ACTIVE                              
500,6        oadv1qtc/APP           28456   APPLSYS.AQ$_FND_CP_GSM_IPC_AQTBL_T  Row Exclusive ACTIVE                              
19,112       oadv1qtc/APP 2007      27500   APPLSYS.FND_CONCURRENT_REQUESTS     Row Exclusive INACTIVE                            
106,6631     /AQADM                 26368   AQADM.AQ$_OE_CMRS_INCR_CUST_ST_I    Row Exclusive ACTIVE                              
512,6        oadv1qtc/APP           28484   APPLSYS.AQ$_FND_CP_GSM_OPP_AQTBL_H  Row Exclusive ACTIVE                              
512,6        oadv1qtc/APP           28484   APPLSYS.AQ$_FND_CP_GSM_OPP_AQTBL_T  Row Exclusive ACTIVE                              
106,6631     /AQADM                 26368   AQADM.AQ$_OE_CMRS_INCR_CUST_ST_H    Row Exclusive ACTIVE                              
106,6631     /AQADM                 26368   AQADM.AQ$_OE_CMRS_INCR_CUST_ST_T    Row Exclusive ACTIVE                              
106,6631     /AQADM                 26368   AQADM.AQ$_CCAXB11_ST_I              Row Exclusive ACTIVE                              
106,6631     /AQADM                 26368   AQADM.AQ$_CCAXB11_ST_H              Row Exclusive ACTIVE                              
106,6631     /AQADM                 26368   AQADM.AQ$_CCAXB11_ST_T              Row Exclusive ACTIVE                              
207,185      oadv1qtc/APP           27763   APPS.JTF_FM_RAPID_RQ_QTBL           Row Exclusive ACTIVE                              
549,13404    oadv1qtc/APP 21933     15264   GESADM.MLOG$_XXCMF_ATP_LINES_ARCHIV Row Exclusive INACTIVE                            
179,2063     oadv1qtc/APP 11261     4449    GESADM.MLOG$_XXCMF_ATP_LINES_ARCHIV Row Exclusive INACTIVE                            
549,13404    oadv1qtc/APP 21933     15264   CMF.XXCMF_ATP_LINES_ARCHIVE         Row Exclusive INACTIVE                            
179,2063     oadv1qtc/APP 11261     4449    CMF.XXCMF_ATP_HEADERS_ARCHIVE       Row Exclusive INACTIVE                            
549,13404    oadv1qtc/APP 21933     15264   CMF.XXCMF_ATP_HEADERS_ARCHIVE       Row Exclusive INACTIVE                            
90,4133      oadv1qtc/APP 11987     4953    GESADM.MLOG$_XXCMF_ATP_LINES_ARCHIV Row Exclusive INACTIVE                            
90,4133      oadv1qtc/APP 11987     4953    CMF.XXCMF_ATP_HEADERS_ARCHIVE       Row Exclusive INACTIVE                            
90,4133      oadv1qtc/APP 11987     4953    CMF.XXCMF_ATP_LINES_ARCHIVE         Row Exclusive INACTIVE                            

37 rows selected.

SELECT /*+ ordered */
        –b.kaddr,
        c.sid,
        lock_waiter.waiting_session,
        lock_blocker.holding_session,
        c.program,
        c.osuser,
        c.machine,
        c.process,
        decode(u.name,
                NULL,”,
                u.name”‘.’”o.name
        ) object,
        c.username,
        decode
        (
                b.type,
                ‘BL’, ‘Buffer hash table instance lock’,
                ‘CF’, ‘Control file schema global enqueue lock’,
                ‘CI’, ‘Cross-instance function invocation instance lock’,
                ‘CU’, ‘Cursor bind lock’,
                ‘DF’, ‘Data file instance lock’,
                ‘DL’, ‘direct loader parallel index create lock’,
                ‘DM’, ‘Mount/startup db primary/secondary instance lock’,
                ‘DR’, ‘Distributed recovery process lock’,
                ‘DX’, ‘Distributed transaction entry lock’,
                ‘FS’, ‘File set 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’,
                ‘LA’,‘Library cache lock instance lock (A..P=namespace);’,
                ‘LB’,‘Library cache lock instance lock (A..P=namespace);’,
                ‘LC’,‘Library cache lock instance lock (A..P=namespace);’,
                ‘LD’,‘Library cache lock instance lock (A..P=namespace);’,
                ‘LE’,‘Library cache lock instance lock (A..P=namespace);’,
                ‘LF’,‘Library cache lock instance lock (A..P=namespace);’,
                ‘LG’,‘Library cache lock instance lock (A..P=namespace);’,
                ‘LH’,‘Library cache lock instance lock (A..P=namespace);’,
                ‘LI’,‘Library cache lock instance lock (A..P=namespace);’,
                ‘LJ’,‘Library cache lock instance lock (A..P=namespace);’,
                ‘LK’,‘Library cache lock instance lock (A..P=namespace);’,
                ‘LL’,‘Library cache lock instance lock (A..P=namespace);’,
                ‘LM’,‘Library cache lock instance lock (A..P=namespace);’,
                ‘LN’,‘Library cache lock instance lock (A..P=namespace);’,
                ‘LO’,‘Library cache lock instance lock (A..P=namespace);’,
                ‘LP’,‘Library cache lock instance lock (A..P=namespace);’,
                ‘MM’, ‘Mount definition global enqueue lock’,
                ‘MR’, ‘Media recovery lock’,
                ‘NA’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NB’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NC’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘ND’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NE’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NF’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NG’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NH’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NI’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NJ’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NK’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NL’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NM’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NN’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NO’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NP’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NQ’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NR’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NS’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NT’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NU’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NV’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NW’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NX’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NY’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NZ’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘PF’, ‘Password File lock’,
                ‘PI’, ‘Parallel operation locks’,
                ‘PS’, ‘Parallel operation locks’,
                ‘PR’, ‘Process startup lock’,
                ‘QA’,‘Row cache instance lock (A..Z=cache)’,
                ‘QB’,‘Row cache instance lock (A..Z=cache)’,
                ‘QC’,‘Row cache instance lock (A..Z=cache)’,
                ‘QD’,‘Row cache instance lock (A..Z=cache)’,
                ‘QE’,‘Row cache instance lock (A..Z=cache)’,
                ‘QF’,‘Row cache instance lock (A..Z=cache)’,
                ‘QG’,‘Row cache instance lock (A..Z=cache)’,
                ‘QH’,‘Row cache instance lock (A..Z=cache)’,
                ‘QI’,‘Row cache instance lock (A..Z=cache)’,
                ‘QJ’,‘Row cache instance lock (A..Z=cache)’,
                ‘QK’,‘Row cache instance lock (A..Z=cache)’,
                ‘QL’,‘Row cache instance lock (A..Z=cache)’,
                ‘QM’,‘Row cache instance lock (A..Z=cache)’,
                ‘QN’,‘Row cache instance lock (A..Z=cache)’,
                ‘QP’,‘Row cache instance lock (A..Z=cache)’,
                ‘QQ’,‘Row cache instance lock (A..Z=cache)’,
                ‘QR’,‘Row cache instance lock (A..Z=cache)’,
                ‘QS’,‘Row cache instance lock (A..Z=cache)’,
                ‘QT’,‘Row cache instance lock (A..Z=cache)’,
                ‘QU’,‘Row cache instance lock (A..Z=cache)’,
                ‘QV’,‘Row cache instance lock (A..Z=cache)’,
                ‘QW’,‘Row cache instance lock (A..Z=cache)’,
                ‘QX’,‘Row cache instance lock (A..Z=cache)’,
                ‘QY’,‘Row cache instance lock (A..Z=cache)’,
                ‘QZ’,‘Row cache instance lock (A..Z=cache)’,
                ‘RT’, ‘Redo thread global enqueue lock’,
                ‘SC’, ‘System commit number instance lock’,
                ‘SM’, ‘SMON lock’,
                ‘SN’, ‘Sequence number instance lock’,
                ‘SQ’, ‘Sequence number enqueue lock’,
                ‘SS’, ‘Sort segment locks’,
                ‘ST’, ‘Space transaction enqueue lock’,
                ‘SV’, ‘Sequence number value lock’,
                ‘TA’, ‘Generic enqueue lock’,
                ‘TS’, ‘Temporary segment enqueue lock (ID2=0)’,
                ‘TS’, ‘New block allocation enqueue lock (ID2=1)’,
                ‘TT’, ‘Temporary table enqueue lock’,
                ‘UN’, ‘User name lock’,
                ‘US’, ‘Undo segment DDL lock’,
                ‘WL’, ‘Being-written redo log instance lock’,
                b.type
        ) lock_type,
        decode
        (
                b.lmode,
                0, ‘None’,           /* Mon Lock equivalent */
                1, ‘Null’,           /* N */
                2, ‘Row-S (SS)’,     /* L */
                3, ‘Row-X (SX)’,     /* R */
                4, ‘Share’,          /* S */
                5, ‘S/Row-X (SRX)’,  /* C */
                6, ‘Exclusive’,      /* X */
                to_char(b.lmode)
        ) mode_held,
        decode
        (
                b.request,
                0, ‘None’,           /* Mon Lock equivalent */
                1, ‘Null’,           /* N */
                2, ‘Row-S (SS)’,     /* L */
                3, ‘Row-X (SX)’,     /* R */
                4, ‘Share’,          /* S */
                5, ‘S/Row-X (SSX)’,  /* C */
                6, ‘Exclusive’,      /* X */
                to_char(b.request)
        ) mode_requested
FROM
        v$lock b
        ,v$session c
        ,sys.user$ u
        ,sys.obj$ o
        ,( SELECT * FROM sys.dba_waiters) lock_blocker
        ,( SELECT * FROM sys.dba_waiters) lock_waiter
WHERE
b.sid = c.sid
AND u.user# = c.user#
AND o.obj#(+) = b.id1
AND lock_blocker.waiting_session(+) = c.sid
AND lock_waiter.holding_session(+) = c.sid
AND c.username != ‘SYS’
ORDER BY kaddr, lockwait;