SELECT
'ALTER SYSTEM KILL SESSION '''||V$SESSION.sid||','||V$SESSION.serial#||''' IMMEDIATE; ' AS kill_command,
a.session_id,
a.oracle_username,
a.os_user_name,
b.owner "OBJECT OWNER",
b.object_name,
b.object_type,
a.locked_mode
FROM (SELECT object_id, session_id, oracle_username, os_user_name, locked_mode
FROM V$LOCKED_OBJECT) a,
(SELECT object_id, owner, object_name,object_type
FROM DBA_OBJECTS) b,
V$SESSION
WHERE a.object_id=b.object_id
AND a.session_id = V$SESSION.sid;
This is working, tested!
SESSION_ID ORACLE_USERNAME OS_USER_NAME OBJECT OWNER OBJECT_NAME OBJECT_TYPE LOCKED_MODE
---------- ---------------- ------------- ------------- ------------- ------------ -----------
592 ORA_USER linux_user ORA_USER TABLE_A TABLE 3
592 ORA_USER linux_user ORA_USER TABLE_B TABLE 0
441 ORA_USER linux_user ORA_USER TABLE_B TABLE 6
V$LOCKED_OBJECT Oracle Reference
lock mode
0 - lock requested but not yet obtained
3 - ROW_X (SX): Row Exclusive Table Lock
6 - Exclusive (X): Exclusive Table Lock
General
Update same table from two sessions
How to find the locking and the locked session?
Flow
Run UPDATE statement without commit, and find the uncommitted session.
From sqlplus:
SQL> UPDATE ADDRESS SET addr_zip_code = '21213' WHERE addr_id = 1;
> no commit!!
SELECT * FROM V$SESSION
WHERE username = 'ALEC_USER'
AND program = 'sqlplus.exe'
The sid and serial# are:
sid: 205
serial#: 21296
==========================
Relate Locking Session ID to SQL Text
==========================
SQL Text could be found in several tables:
V$SQLAREA
V$SQLTEXT
V$SQLTEXT_WITH_NEWLINES
--Ignore INACTIVE Sessions
SELECT SESSIONS.sid||'-'|| SESSIONS.serial# AS SID_SERIAL#,
SESSIONS.username,
SESSIONS.osuser,
SESSIONS.machine,
SESSIONS.program,
SESSIONS.module,
REPLACE(SQLTEXT.sql_text,CHR(10),'') sql_text
FROM V$SQLAREA SQLTEXT,
V$SESSION SESSIONS
WHERE SQLTEXT.address = SESSIONS.sql_address
AND SQLTEXT.hash_value = SESSIONS.prev_hash_value
AND SESSIONS.status != 'INACTIVE' --Ignore only INACTIVE Sessions
AND SESSIONS.audsid <> USERENV('sessionid') --Ignore current session
ORDER BY SQLTEXT.address
--For INACTIVE Sessions
SELECT SESSIONS.sid||'-'|| SESSIONS.serial# AS SID_SERIAL#,
SESSIONS.username,
SESSIONS.osuser,
SESSIONS.machine,
SESSIONS.program,
SESSIONS.module,
REPLACE(SQLTEXT.sql_text,CHR(10),'') sql_text
FROM V$SQLAREA SQLTEXT,
V$SESSION SESSIONS
WHERE SQLTEXT.address = SESSIONS.prev_sql_addr
AND SQLTEXT.hash_value = SESSIONS.prev_hash_value
AND SESSIONS.status = 'INACTIVE'
ORDER BY SQLTEXT.address
==========================
Find locking and locked sessions
==========================
Option A.
Use V$LOCK table as parent/child relation.
SESSION_ID ORACLE_USERNAME OS_USER_NAME OBJECT OWNER OBJECT_NAME OBJECT_TYPE LOCKED_MODE
---------- ---------------- ------------- ------------- ------------- ------------ -----------
592 ORA_USER linux_user ORA_USER TABLE_A TABLE 3
592 ORA_USER linux_user ORA_USER TABLE_B TABLE 0
441 ORA_USER linux_user ORA_USER TABLE_B TABLE 6
V$LOCKED_OBJECT Oracle Reference
lock mode
0 - lock requested but not yet obtained
3 - ROW_X (SX): Row Exclusive Table Lock
6 - Exclusive (X): Exclusive Table Lock
General
Update same table from two sessions
How to find the locking and the locked session?
Flow
Run UPDATE statement without commit, and find the uncommitted session.
From sqlplus:
SQL> UPDATE ADDRESS SET addr_zip_code = '21213' WHERE addr_id = 1;
> no commit!!
SELECT * FROM V$SESSION
WHERE username = 'ALEC_USER'
AND program = 'sqlplus.exe'
The sid and serial# are:
sid: 205
serial#: 21296
==========================
Relate Locking Session ID to SQL Text
==========================
SQL Text could be found in several tables:
V$SQLAREA
V$SQLTEXT
V$SQLTEXT_WITH_NEWLINES
--Ignore INACTIVE Sessions
SELECT SESSIONS.sid||'-'|| SESSIONS.serial# AS SID_SERIAL#,
SESSIONS.username,
SESSIONS.osuser,
SESSIONS.machine,
SESSIONS.program,
SESSIONS.module,
REPLACE(SQLTEXT.sql_text,CHR(10),'') sql_text
FROM V$SQLAREA SQLTEXT,
V$SESSION SESSIONS
WHERE SQLTEXT.address = SESSIONS.sql_address
AND SQLTEXT.hash_value = SESSIONS.prev_hash_value
AND SESSIONS.status != 'INACTIVE' --Ignore only INACTIVE Sessions
AND SESSIONS.audsid <> USERENV('sessionid') --Ignore current session
ORDER BY SQLTEXT.address
SELECT SESSIONS.sid||'-'|| SESSIONS.serial# AS SID_SERIAL#,
SESSIONS.username,
SESSIONS.osuser,
SESSIONS.machine,
SESSIONS.program,
SESSIONS.module,
REPLACE(SQLTEXT.sql_text,CHR(10),'') sql_text
FROM V$SQLAREA SQLTEXT,
V$SESSION SESSIONS
WHERE SQLTEXT.address = SESSIONS.prev_sql_addr
AND SQLTEXT.hash_value = SESSIONS.prev_hash_value
AND SESSIONS.status = 'INACTIVE'
ORDER BY SQLTEXT.address
==========================
Find locking and locked sessions
==========================
Option A.
Use V$LOCK table as parent/child relation.
block-1 - blocking others
block-0 - not blocking others
SELECT
LOCKING.SID AS LOCKING_SID,
LOCKED.SID AS LOCKED_SID,
LOCKING.type
FROM V$LOCK LOCKING,
V$LOCK LOCKED
WHERE 1=1
AND LOCKING.block=1
AND LOCKED.block=0
AND LOCKING.ID1||LOCKING.ID2 = LOCKED.ID1||LOCKED.ID2;
LOCKING_SID LOCKED_SID TYPE
----------- ---------- ----
194 205 TX
Elapsed: 00:04:00.03
Option B.
Use UNION ALL between V$LOCK LOCKS and V$LOCK LOCKED.
This one runs much faster, approx 2 seconds.
LOCK_STATUS LOCK_ID1_ID2 SID TYPE SID_SERIAL USERNAME OSUSER MACHINE
------------ --------------- ---- ---- ---------- ---------- ---------- -------------
OK 196623-379112 194 TX 194-9665 ALEC_USER akaplan 1\ALEC-KAPLAN
Being Locked 196623-379112 205 TX 205-21296 ALEC_USER akaplan 1\ALEC-KAPLAN
PROGRAM MODULE SQL_TEXT
----------- ---------- ----------------------------------------
sqlplus.exe SQL*Plus commit
sqlplus.exe SQL*Plus UPDATE ADDRESS SET addr_zip_code = '212
Elapsed: 00:00:02.00
==========================
Find locking and locked Objects
==========================
SELECT LOCKED_SESSIONS.lock_status,
id1||'-'||id2 LOCK_ID1_ID2,
LOCKED_SESSIONS.sid,
LOCKED_SESSIONS.type,
SESSIONS.sid||'-'|| SESSIONS.serial# AS SID_SERIAL#,
SESSIONS.username,
SESSIONS.osuser,
SESSIONS.machine,
SESSIONS.program,
SESSIONS.module,
OBJECTS.owner,
OBJECTS.object_name,
OBJECTS.object_type AS TYPE,
LOCKED_OBJECTS.process,
PROCESS.spid
FROM(
SELECT
DECODE(BEING_LOCKED.block,0,'OK','Being Locked') AS LOCK_STATUS,
BEING_LOCKED.SID AS SID,
BEING_LOCKED.type,
BEING_LOCKED.ID1,
BEING_LOCKED.ID2,
BEING_LOCKED.lmode,
BEING_LOCKED.request,
BEING_LOCKED.block
FROM V$LOCK BEING_LOCKED
WHERE 1=1
AND BEING_LOCKED.block=1
UNION ALL
SELECT
DECODE(LOCKS.block,0,'OK','Being Locked') AS LOCK_STATUS,
LOCKS.SID AS SID,
LOCKS.type,
LOCKS.ID1,
LOCKS.ID2,
LOCKS.lmode,
LOCKS.request,
LOCKS.block
FROM V$LOCK LOCKS
WHERE 1=1
AND LOCKS.block=0
AND ID1||ID2 IN
(SELECT ID1||ID2 FROM V$LOCK BEING_LOCKED WHERE BEING_LOCKED.block = 1)
AND LOCKS.block=0
)LOCKED_SESSIONS,
V$SESSION SESSIONS,
DBA_OBJECTS OBJECTS,
V$LOCKED_OBJECT LOCKED_OBJECTS,
V$PROCESS PROCESS
WHERE SESSIONS.sid = LOCKED_SESSIONS.sid
AND LOCKED_SESSIONS.sid = LOCKED_OBJECTS.session_id
AND OBJECTS.object_id = LOCKED_OBJECTS.object_id
AND PROCESS.addr = SESSIONS.paddr;
LOCK_STATUS LOCK_ID1_ID2 SID TYPE SID_SERIAL USERNAME OSUSER
------------ --------------- ---------- ---------- ---------- ---------- ----------
OK 196623-379112 194 TX 194-9665 ALEC_USER akaplan
Being Locked 196623-379112 205 TX 205-21296 ALEC_USER akaplan
Being Locked 196623-379112 205 TX 205-21296 ALEC_USER akaplan
MACHINE PROGRAM MODULE OWNER OBJECT_NAME TYPE PROCESS SPID
------------- ----------- ---------- ---------- --------------- ------ --------- -----
1\ALEC-KAPLAN sqlplus.exe SQL*Plus ALEC_USER ADDRESS TABLE 4052:2348 30417
1\ALEC-KAPLAN sqlplus.exe SQL*Plus ALEC_USER ADDRESS TABLE 2176:3308 20300
1\ALEC-KAPLAN sqlplus.exe SQL*Plus ALEC_USER MLOG$_ADDRESS TABLE 2176:3308 20300
The SPID column is the Server Process ID
The PROCESS column is the Client Process ID:Thread ID
In this example, the Client is on Windows.
SPID - is the Process on Oracle server:
oracle@my_server:~>% ps -ef | grep oracle | grep 30417 | grep -v grep
oracle 30417 1 0 09:12 ? 00:00:00 oracleigt (LOCAL=NO)
oracle@my_server:~>% ps -ef | grep 20300 | grep -v grep
oracle 20300 1 0 Oct05 ? 00:00:01 oracleigt (LOCAL=NO)
The PROCESS column is the Client Process ID:Thread ID
In this example, the Windows Process:Thread.
This is the Process running on the Windows Client, as captured with Process Explorer.
The locking process is 4052:2348, i.e. process 4052, thread:2348
The locked process is 2176:3308, i.e. process 2176, thread:3308
=================
V$TRANSACTION Table
=================
LOCK_STATUS LOCK_ID1_ID2 SID TYPE SID_SERIAL USERNAME OSUSER MACHINE PROGRAM MODULE
------------ ------------- --- ---- ---------- --------- ------- ------------- ----------- --------
Being Locked 196623-379112 205 TX 205-21296 ALEC_USER akaplan 1\ALEC-KAPLAN sqlplus.exe SQL*Plus
Being Locked 196623-379112 205 TX 205-21296 ALEC_USER akaplan 1\ALEC-KAPLAN sqlplus.exe SQL*Plus
OWNER OBJECT_NAME OBJECT_TYPE PROCESS SPID TX_START_DATE TX_STATUS START_SCN
--------- ------------- ---------- --------- ----- ------------------- --------- ------------
ALEC_USER ADDRESS TABLE 2176:3308 20300 06/10/2014 08:10:43 ACTIVE 230682831978
ALEC_USER MLOG$_ADDRESS TABLE 2176:3308 20300 06/10/2014 08:10:43 ACTIVE 230682831978
==========================
V$LOCK Table in details
==========================
V$LOCK.block
0 - The session is not blocking other sessions
1 - The session is blocking other sessions
V$LOCK.request
0 - The session is not requesting for a lock
1-6 - The session is requesting for a lock. The number gives additional info.
V$LOCK.lmode
0 - The session is not holding a lock
1-6 - The sessions is holding a lock. The number gives additional info.
V$LOCK.id1 and V$LOCK.id2
When sessions A is locking sessions B - sessions could be related by valID1 and ID2 values.
V$LOCK.sid
Session Identifier
V$LOCK.type
User Type Lock or System Type Lock
- User type locks:
TM - DML enqueue
TX - Transaction enqueue
UL - User supplied
- System Types Locks
Are held for extremely short periods of time. They are listed below.
BL - Buffer hash table instance
NA..NZ - Library cache pin instance (A..Z = namespace)
CF - Control file schema global enqueue
PF - Password File
CI - Cross-instance function invocation instance
PI, PS - Parallel operation
CU - Cursor bind
PR - Process startup
DF - datafile instance
QA..QZ - Row cache instance (A..Z = cache)
DL - Direct loader parallel index create
RT - Redo thread global enqueue
DM - Mount/startup db primary/secondary instance
SC - System change number instance
DR - Distributed recovery process
SM - SMON
DX - Distributed transaction entry
SN - Sequence number instance
FS - File set
SQ - Sequence number enqueue
HW - Space management operations on a specific segment
SS - Sort segment
IN - Instance number
ST - Space transaction enqueue
IR - Instance recovery serialization global enqueue
SV - Sequence number value
IS - Instance state
TA - Generic enqueue
IV - Library cache invalidation instance
TS - Temporary segment enqueue (ID2=0)
JQ - Job queue
TS - New block allocation enqueue (ID2=1)
KK - Thread kick
TT - Temporary table enqueue
LA .. LP - Library cache lock instance lock (A..P = namespace)
UN - User name
MM - Mount definition global enqueue
US - Undo segment DDL
MR - Media recovery
LMODE NUMBER Lock mode in which the session holds the lock:
0 - none
1 - null (NULL)
2 - row-S (SS)
3 - row-X (SX)
4 - share (S)
5 - S/Row-X (SSX)
6 - exclusive (X)
REQUEST
REQUEST NUMBER Lock mode in which the process requests the lock:
0 - none
1 - null (NULL)
2 - row-S (SS)
3 - row-X (SX)
4 - share (S)
5 - S/Row-X (SSX)
6 - exclusive (X)
==========
Appendix
==========
http://www.orafaq.com/node/854
http://select-star-from.blogspot.co.il/2013/07/how-to-find-sqlsqlid-history-on-oracle.html
SELECT
LOCKING.SID AS LOCKING_SID,
LOCKED.SID AS LOCKED_SID,
LOCKING.type
FROM V$LOCK LOCKING,
V$LOCK LOCKED
WHERE 1=1
AND LOCKING.block=1
AND LOCKED.block=0
AND LOCKING.ID1||LOCKING.ID2 = LOCKED.ID1||LOCKED.ID2;
LOCKING_SID LOCKED_SID TYPE
----------- ---------- ----
194 205 TX
Elapsed: 00:04:00.03
Option B.
Use UNION ALL between V$LOCK LOCKS and V$LOCK LOCKED.
This one runs much faster, approx 2 seconds.
SELECT LOCKED_SESSIONS.lock_status,
LOCKED_SESSIONS.sid,
LOCKED_SESSIONS.type,
SESSIONS.sid||','|| SESSIONS.serial# AS SID_SERIAL#, SESSIONS.username, osuser, machine, program, SESSIONS.module,
SUBSTR(REPLACE(SQLTEXT.sql_text,CHR(10),''),0,400) AS SQL_TEXT
FROM(
SELECT
DECODE(LOCKING.block,0,'Not Locking','Locking') AS LOCK_STATUS,
LOCKING.SID AS SID,
LOCKING.type,
LOCKING.ID1,
LOCKING.ID2,
LOCKING.lmode,
LOCKING.request,
LOCKING.block
FROM V$LOCK LOCKING
WHERE 1=1
AND LOCKING.block=1
UNION ALL
SELECT
DECODE(LOCKED.block,0,'Not Locking','Locking') AS LOCK_STATUS,
LOCKED.SID AS SID,
LOCKED.type,
LOCKED.ID1,
LOCKED.ID2,
LOCKED.lmode,
LOCKED.request,
LOCKED.block
FROM V$LOCK LOCKED
WHERE 1=1
AND LOCKED.block=0
AND ID1||ID2 IN
(SELECT ID1||ID2 FROM V$LOCK WHERE V$LOCK.block = 1)
)LOCKED_SESSIONS,
V$SESSION SESSIONS,
V$SQLAREA SQLTEXT
WHERE SESSIONS.sid = LOCKED_SESSIONS.sid
--AND SESSIONS.username = 'ALEC_USER'
--AND SESSIONS.program = 'sqlplus.exe'
AND SQLTEXT.address = SESSIONS.prev_sql_addr
AND SQLTEXT.hash_value = SESSIONS.prev_hash_value
ORDER BY 1
LOCK_STATUS LOCK_ID1_ID2 SID TYPE SID_SERIAL USERNAME OSUSER MACHINE
------------ --------------- ---- ---- ---------- ---------- ---------- -------------
OK 196623-379112 194 TX 194-9665 ALEC_USER akaplan 1\ALEC-KAPLAN
Being Locked 196623-379112 205 TX 205-21296 ALEC_USER akaplan 1\ALEC-KAPLAN
PROGRAM MODULE SQL_TEXT
----------- ---------- ----------------------------------------
sqlplus.exe SQL*Plus commit
sqlplus.exe SQL*Plus UPDATE ADDRESS SET addr_zip_code = '212
Elapsed: 00:00:02.00
==========================
Find locking and locked Objects
==========================
SELECT LOCKED_SESSIONS.lock_status,
id1||'-'||id2 LOCK_ID1_ID2,
LOCKED_SESSIONS.sid,
LOCKED_SESSIONS.type,
SESSIONS.sid||'-'|| SESSIONS.serial# AS SID_SERIAL#,
SESSIONS.username,
SESSIONS.osuser,
SESSIONS.machine,
SESSIONS.program,
SESSIONS.module,
OBJECTS.owner,
OBJECTS.object_name,
OBJECTS.object_type AS TYPE,
LOCKED_OBJECTS.process,
PROCESS.spid
FROM(
SELECT
DECODE(BEING_LOCKED.block,0,'OK','Being Locked') AS LOCK_STATUS,
BEING_LOCKED.SID AS SID,
BEING_LOCKED.type,
BEING_LOCKED.ID1,
BEING_LOCKED.ID2,
BEING_LOCKED.lmode,
BEING_LOCKED.request,
BEING_LOCKED.block
FROM V$LOCK BEING_LOCKED
WHERE 1=1
AND BEING_LOCKED.block=1
UNION ALL
SELECT
DECODE(LOCKS.block,0,'OK','Being Locked') AS LOCK_STATUS,
LOCKS.SID AS SID,
LOCKS.type,
LOCKS.ID1,
LOCKS.ID2,
LOCKS.lmode,
LOCKS.request,
LOCKS.block
FROM V$LOCK LOCKS
WHERE 1=1
AND LOCKS.block=0
AND ID1||ID2 IN
(SELECT ID1||ID2 FROM V$LOCK BEING_LOCKED WHERE BEING_LOCKED.block = 1)
AND LOCKS.block=0
)LOCKED_SESSIONS,
V$SESSION SESSIONS,
DBA_OBJECTS OBJECTS,
V$LOCKED_OBJECT LOCKED_OBJECTS,
V$PROCESS PROCESS
WHERE SESSIONS.sid = LOCKED_SESSIONS.sid
AND LOCKED_SESSIONS.sid = LOCKED_OBJECTS.session_id
AND OBJECTS.object_id = LOCKED_OBJECTS.object_id
AND PROCESS.addr = SESSIONS.paddr;
LOCK_STATUS LOCK_ID1_ID2 SID TYPE SID_SERIAL USERNAME OSUSER
------------ --------------- ---------- ---------- ---------- ---------- ----------
OK 196623-379112 194 TX 194-9665 ALEC_USER akaplan
Being Locked 196623-379112 205 TX 205-21296 ALEC_USER akaplan
Being Locked 196623-379112 205 TX 205-21296 ALEC_USER akaplan
MACHINE PROGRAM MODULE OWNER OBJECT_NAME TYPE PROCESS SPID
------------- ----------- ---------- ---------- --------------- ------ --------- -----
1\ALEC-KAPLAN sqlplus.exe SQL*Plus ALEC_USER ADDRESS TABLE 4052:2348 30417
1\ALEC-KAPLAN sqlplus.exe SQL*Plus ALEC_USER ADDRESS TABLE 2176:3308 20300
1\ALEC-KAPLAN sqlplus.exe SQL*Plus ALEC_USER MLOG$_ADDRESS TABLE 2176:3308 20300
The SPID column is the Server Process ID
The PROCESS column is the Client Process ID:Thread ID
In this example, the Client is on Windows.
SPID - is the Process on Oracle server:
oracle@my_server:~>% ps -ef | grep oracle | grep 30417 | grep -v grep
oracle 30417 1 0 09:12 ? 00:00:00 oracleigt (LOCAL=NO)
oracle@my_server:~>% ps -ef | grep 20300 | grep -v grep
oracle 20300 1 0 Oct05 ? 00:00:01 oracleigt (LOCAL=NO)
The PROCESS column is the Client Process ID:Thread ID
In this example, the Windows Process:Thread.
This is the Process running on the Windows Client, as captured with Process Explorer.
The locking process is 4052:2348, i.e. process 4052, thread:2348
The locked process is 2176:3308, i.e. process 2176, thread:3308
V$TRANSACTION Table
=================
V$TRANSACTION Table holds additional info regarding the locked and the locking transactions.
SELECT LOCKED_SESSIONS.lock_status,
id1||'-'||id2 LOCK_ID1_ID2,
LOCKED_SESSIONS.sid,
LOCKED_SESSIONS.type,
SESSIONS.sid||'-'|| SESSIONS.serial# AS SID_SERIAL#,
SESSIONS.username, SESSIONS.osuser, SESSIONS.machine,
SESSIONS.program, SESSIONS.module,
OBJECTS.owner,
OBJECTS.object_name,
OBJECTS.object_type,
LOCKED_OBJECTS.process,
PROCESS.spid,
TO_CHAR(TRANSACTION.start_date,'DD/MM/YYYY hh24:mm:ss') AS TX_START_DATE,
TRANSACTION.status AS TX_STATUS,
TRANSACTION.start_scn
FROM(
SELECT
DECODE(BEING_LOCKED.block,0,'OK','Being Locked') AS LOCK_STATUS,
BEING_LOCKED.SID AS SID,
BEING_LOCKED.type,
BEING_LOCKED.ID1,
BEING_LOCKED.ID2,
BEING_LOCKED.lmode,
BEING_LOCKED.request,
BEING_LOCKED.block
FROM V$LOCK BEING_LOCKED
WHERE 1=1
AND BEING_LOCKED.block=1
UNION ALL
SELECT
DECODE(LOCKS.block,0,'OK','Being Locked') AS LOCK_STATUS,
LOCKS.SID AS SID,
LOCKS.type,
LOCKS.ID1,
LOCKS.ID2,
LOCKS.lmode,
LOCKS.request,
LOCKS.block
FROM V$LOCK LOCKS
WHERE 1=1
AND LOCKS.block=0
AND ID1||ID2 IN (SELECT ID1||ID2 FROM V$LOCK BEING_LOCKED WHERE BEING_LOCKED.block = 1)
AND LOCKS.block=0
)LOCKED_SESSIONS,
V$SESSION SESSIONS,
DBA_OBJECTS OBJECTS,
V$LOCKED_OBJECT LOCKED_OBJECTS,
V$PROCESS PROCESS,
V$TRANSACTION TRANSACTION
WHERE SESSIONS.sid = LOCKED_SESSIONS.sid
AND LOCKED_SESSIONS.sid = LOCKED_OBJECTS.session_id
AND SESSIONS.username = 'ALEC_USER'
AND SESSIONS.program = 'sqlplus.exe'
AND OBJECTS.object_id = LOCKED_OBJECTS.object_id
AND PROCESS.addr = SESSIONS.paddr
AND SESSIONS.saddr = TRANSACTION.ses_addr
LOCKED_SESSIONS.sid,
LOCKED_SESSIONS.type,
SESSIONS.sid||'-'|| SESSIONS.serial# AS SID_SERIAL#,
SESSIONS.username, SESSIONS.osuser, SESSIONS.machine,
SESSIONS.program, SESSIONS.module,
OBJECTS.owner,
OBJECTS.object_name,
OBJECTS.object_type,
LOCKED_OBJECTS.process,
PROCESS.spid,
TO_CHAR(TRANSACTION.start_date,'DD/MM/YYYY hh24:mm:ss') AS TX_START_DATE,
TRANSACTION.status AS TX_STATUS,
TRANSACTION.start_scn
FROM(
SELECT
DECODE(BEING_LOCKED.block,0,'OK','Being Locked') AS LOCK_STATUS,
BEING_LOCKED.SID AS SID,
BEING_LOCKED.type,
BEING_LOCKED.ID1,
BEING_LOCKED.ID2,
BEING_LOCKED.lmode,
BEING_LOCKED.request,
BEING_LOCKED.block
FROM V$LOCK BEING_LOCKED
WHERE 1=1
AND BEING_LOCKED.block=1
UNION ALL
SELECT
DECODE(LOCKS.block,0,'OK','Being Locked') AS LOCK_STATUS,
LOCKS.SID AS SID,
LOCKS.type,
LOCKS.ID1,
LOCKS.ID2,
LOCKS.lmode,
LOCKS.request,
LOCKS.block
FROM V$LOCK LOCKS
WHERE 1=1
AND LOCKS.block=0
AND ID1||ID2 IN (SELECT ID1||ID2 FROM V$LOCK BEING_LOCKED WHERE BEING_LOCKED.block = 1)
AND LOCKS.block=0
)LOCKED_SESSIONS,
V$SESSION SESSIONS,
DBA_OBJECTS OBJECTS,
V$LOCKED_OBJECT LOCKED_OBJECTS,
V$PROCESS PROCESS,
V$TRANSACTION TRANSACTION
WHERE SESSIONS.sid = LOCKED_SESSIONS.sid
AND LOCKED_SESSIONS.sid = LOCKED_OBJECTS.session_id
AND SESSIONS.username = 'ALEC_USER'
AND SESSIONS.program = 'sqlplus.exe'
AND OBJECTS.object_id = LOCKED_OBJECTS.object_id
AND PROCESS.addr = SESSIONS.paddr
AND SESSIONS.saddr = TRANSACTION.ses_addr
LOCK_STATUS LOCK_ID1_ID2 SID TYPE SID_SERIAL USERNAME OSUSER MACHINE PROGRAM MODULE
------------ ------------- --- ---- ---------- --------- ------- ------------- ----------- --------
Being Locked 196623-379112 205 TX 205-21296 ALEC_USER akaplan 1\ALEC-KAPLAN sqlplus.exe SQL*Plus
Being Locked 196623-379112 205 TX 205-21296 ALEC_USER akaplan 1\ALEC-KAPLAN sqlplus.exe SQL*Plus
OWNER OBJECT_NAME OBJECT_TYPE PROCESS SPID TX_START_DATE TX_STATUS START_SCN
--------- ------------- ---------- --------- ----- ------------------- --------- ------------
ALEC_USER ADDRESS TABLE 2176:3308 20300 06/10/2014 08:10:43 ACTIVE 230682831978
ALEC_USER MLOG$_ADDRESS TABLE 2176:3308 20300 06/10/2014 08:10:43 ACTIVE 230682831978
==========================
V$LOCK Table in details
==========================
V$LOCK.block
0 - The session is not blocking other sessions
1 - The session is blocking other sessions
V$LOCK.request
0 - The session is not requesting for a lock
1-6 - The session is requesting for a lock. The number gives additional info.
V$LOCK.lmode
0 - The session is not holding a lock
1-6 - The sessions is holding a lock. The number gives additional info.
V$LOCK.id1 and V$LOCK.id2
When sessions A is locking sessions B - sessions could be related by valID1 and ID2 values.
V$LOCK.sid
Session Identifier
V$LOCK.type
User Type Lock or System Type Lock
- User type locks:
TM - DML enqueue
TX - Transaction enqueue
UL - User supplied
- System Types Locks
Are held for extremely short periods of time. They are listed below.
BL - Buffer hash table instance
NA..NZ - Library cache pin instance (A..Z = namespace)
CF - Control file schema global enqueue
PF - Password File
CI - Cross-instance function invocation instance
PI, PS - Parallel operation
CU - Cursor bind
PR - Process startup
DF - datafile instance
QA..QZ - Row cache instance (A..Z = cache)
DL - Direct loader parallel index create
RT - Redo thread global enqueue
DM - Mount/startup db primary/secondary instance
SC - System change number instance
DR - Distributed recovery process
SM - SMON
DX - Distributed transaction entry
SN - Sequence number instance
FS - File set
SQ - Sequence number enqueue
HW - Space management operations on a specific segment
SS - Sort segment
IN - Instance number
ST - Space transaction enqueue
IR - Instance recovery serialization global enqueue
SV - Sequence number value
IS - Instance state
TA - Generic enqueue
IV - Library cache invalidation instance
TS - Temporary segment enqueue (ID2=0)
JQ - Job queue
TS - New block allocation enqueue (ID2=1)
KK - Thread kick
TT - Temporary table enqueue
LA .. LP - Library cache lock instance lock (A..P = namespace)
UN - User name
MM - Mount definition global enqueue
US - Undo segment DDL
MR - Media recovery
LMODE NUMBER Lock mode in which the session holds the lock:
0 - none
1 - null (NULL)
2 - row-S (SS)
3 - row-X (SX)
4 - share (S)
5 - S/Row-X (SSX)
6 - exclusive (X)
REQUEST
REQUEST NUMBER Lock mode in which the process requests the lock:
0 - none
1 - null (NULL)
2 - row-S (SS)
3 - row-X (SX)
4 - share (S)
5 - S/Row-X (SSX)
6 - exclusive (X)
==========
Appendix
==========
http://www.orafaq.com/node/854
http://select-star-from.blogspot.co.il/2013/07/how-to-find-sqlsqlid-history-on-oracle.html
Thanks for sharing, nice post! Post really provice useful information!
ReplyDeleteAn Thái Sơn chia sẻ trẻ sơ sinh nằm nôi điện có tốt không hay võng điện có tốt không và giải đáp cục điện đưa võng giá bao nhiêu cũng như mua máy đưa võng ở tphcm địa chỉ ở đâu uy tín.