In a single-user database, the user can modify data in the database
without concern for other users modifying the same data at the same
time. However, in a multiuser database, the statements within multiple
simultaneous transactions can update the same data. Transactions
executing at the same time need to produce meaningful and consistent
results. Therefore, control of data concurrency and data consistency is
vital in a multiuser database.
* Data concurrency means that many users can access data at the same time.
* Data consistency means that each user sees a consistent view of the data, including visible changes made by the user’s own transactions and transactions of other users.
In general, multiuser databases use some form of data locking to solve the problems associated with data concurrency, consistency, and integrity. Locks are mechanisms that prevent destructive interaction between transactions accessing the same resource.
How do we find locks?
DBA_DML_LOCKS
Displays a session if it is not waiting for a locked object but is holding a lock on an object for which another session is waiting.
Just another way to find the locks
Just to find a raw sql on a table lock!
Another simple scripts to find sesion, serial for locks. locks.sql displays all sessions holding a lock on a table or row.
Knowing the session/serial#, you can kill some sessions unwanted for you!
This produces an output like this
Yet another script that gives more locks information
* Data concurrency means that many users can access data at the same time.
* Data consistency means that each user sees a consistent view of the data, including visible changes made by the user’s own transactions and transactions of other users.
In general, multiuser databases use some form of data locking to solve the problems associated with data concurrency, consistency, and integrity. Locks are mechanisms that prevent destructive interaction between transactions accessing the same resource.
How do we find locks?
DBA_DML_LOCKS
Displays a session if it is not waiting for a locked object but is holding a lock on an object for which another session is waiting.
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;
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;
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
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
Knowing the session/serial#, you can kill some sessions unwanted for you!
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;
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.
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;
–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;
No comments:
Post a Comment