WHICH LOCK MODES ARE REQUIRED FOR WHICH TABLE ACTION?
==========================================================
THE FOLLOWING TABLE DESCRIBES WHAT LOCK MODES ON DML ENQUEUES ARE ACTUALLY
GOTTEN FOR WHICH TABLE OPERATIONS IN A STANDARD ORACLE INSTALLATION.
OPERATION LOCK MODE LMODE LOCK DESCRIPTION
------------------------- --------- ----- ----------------
SELECT NULL 1 NULL
SELECT FOR UPDATE SS 2 SUB SHARE
INSERT SX 3 SUB EXCLUSIVE
UPDATE SX 3 SUB EXCLUSIVE
DELETE SX 3 SUB EXCLUSIVE
LOCK FOR UPDATE SS 2 SUB SHARE
LOCK SHARE S 4 SHARE
LOCK EXCLUSIVE X 6 EXCLUSIVE
LOCK ROW SHARE SS 2 SUB SHARE
LOCK ROW EXCLUSIVE SX 3 SUB EXCLUSIVE
LOCK SHARE ROW EXCLUSIVE SSX 5 SHARE/SUB EXCLUSIVE
ALTER TABLE X 6 EXCLUSIVE
DROP TABLE X 6 EXCLUSIVE
CREATE INDEX S 4 SHARE
DROP INDEX X 6 EXCLUSIVE
TRUNCATE TABLE X 6 EXCLUSIVE
-----------------------------------------------------------
HOW COMPATIBILITY OF LOCKS WORK
=============================================================================
THE COMPATIBILITY OF LOCK MODES ARE NORMALLY REPRESENTED BY FOLLOWING MATRIX:
NULL SS SX S SSX X
-----------------------------------------------------------------------------
NULL YES YES YES YES YES YES
SS YES YES YES YES YES NO
SX YES YES YES NO NO NO
S YES YES NO YES NO NO
SSX YES YES NO NO NO NO
X YES NO NO NO NO NO
Which views can be used to detect locking problems?
A number of Oracle views permits to detect locking problems.
V$SESSION_WAIT When a session is waiting on a resource, it can be found waiting on the enqueue wait event,
e.g. SELECT * FROM V$SESSION_WAIT WHERE EVENT = 'enqueue';
- SID identifier of session holding the lock
- P1, P2, P3 determine the resource when event = 'enqueue'
- SECONDS_IN_WAIT gives how long the wait did occurs
V$SESSION session information and row locking information
- SID, SERIAL# identifier of the session
- LOCKWAIT address of the lock waiting, otherwise null
- ROW_WAIT_OBJ# object identified of the object we are waiting on
(object_id of dba_objects)
- ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# file_id , block_id and
row location within block of the locked row
V$LOCK list of all the locks in the system
- SID identifier of session holding the lock
- TYPE, ID1 and ID2 determine the resource
- LMODE and REQUEST indicate which queue the session is waiting on, as follows:
LMODE > 0, REQUEST = 0 owner
LMODE = 0, REQUEST > 0 acquirer
LMODE > 0, REQUEST > 0 converter
- CTIME time since current mode was converted (see Note 223146.1)
- BLOCK are we blocking another lock
BLOCK = 0 non blocking
BLOCK = 1 blocking others
DBA_LOCK or DBA_LOCKS formatted view on V$LOCK (created via
$ORACLE_HOME/rdbms/admin/catblock.sql)
- SESSION_ID == SID in V$LOCK
- LOCK_TYPE, LOCK_ID1, LOCK_ID2 formatted value of TYPE, ID1, ID2 from V$LOCK
- MODE_HELD and MODE_REQUESTED formatted value of LMODE and REQUEST from V$LOCK
- LAST_CONVERT == CTIME of V$LOCK
- BLOCKING_OTHERS formatted value of BLOCK from V$LOCK
V$TRANSACTION_ENQUEUE subset of V$LOCK for the blocking TX resources only
(same description as for the V$LOCK view)
V$ENQUEUE_LOCK subset of V$LOCK for the system resources only and
blocked TX resources only. (same description as for the V$LOCK view)
DBA_DML_LOCKS subset of the V$LOCK for the DML (TM) locks only
(created via $ORACLE_HOME/rdbms/admin/catblock.sql
- same description as the DBA_LOCK view)
V$LOCKED_OBJECT same info as DBA_DML_LOCKS, but linked with the
rollback and session information
- XIDUSN, XIDSLOT and XIDSQN rollback information to be linked with V$TRANSACTION
- OBJECT_ID object being locked
- SESSION_ID session id
- ORACLE_USERNAME oracle user name
- OS_USER_NAME OS user name
- PROCESS OS process id
- LOCKED_MODE lock mode
V$RESOURCE list of all the currently locked resources in the system.
Each row can be associated with one or more rows in V$LOCK
Each row can be associated with one or more rows in V$LOCK
- TYPE, ID1 and ID2 determine the resource
DBA_DDL_LOCKS has a row for each DDL lock that is being held, and
one row for each outstanding request for a DDL lock. It is subset of DBA_LOCKS
same description as the DBA_LOCK view
DBA_WAITERS view that retrieve information for each session waiting on a
lock (created via $ORACLE_HOME/rdbms/admin/catblock.sql)
- WAITING_SESSION waiting session
- HOLDING_SESSION holding session
- LOCK_TYPE, LOCK_ID1, LOCK_ID2 resource locked
- MODE_HELD lock type held
- MODE_REQUESTED lock type requested
DBA_BLOCKERS view that gives the blocking sessions (created via $ORACLE_HOME/rdbms/admin/catblock.sql)
-HOLDING_SESSION holding session
SELECT B.SID, C.USERNAME, C.OSUSER, C.TERMINAL,
DECODE(B.ID2, 0, A.OBJECT_NAME,
'TRANS-'||TO_CHAR(B.ID1)) OBJECT_NAME,
B.TYPE,
DECODE(B.LMODE,0,'--WAITING--',
1,'NULL',
2,'ROW SHARE',
3,'ROW EXCL',
4,'SHARE',
5,'SHA ROW EXC',
6,'EXCLUSIVE',
'OTHER') "LOCK MODE",
DECODE(B.REQUEST,0,' ',
1,'NULL',
2,'ROW SHARE',
3,'ROW EXCL',
4,'SHARE',
5,'SHA ROW EXC',
6,'EXCLUSIVE',
'OTHER') "REQ MODE"
FROM DBA_OBJECTS A, V$LOCK B, V$SESSION C
WHERE A.OBJECT_ID(+) = B.ID1
AND B.SID = C.SID
AND C.USERNAME IS NOT NULL
ORDER BY B.SID, B.ID2;
SELECT NVL(S.USERNAME,'INTERNAL') USERNAME,
NVL(S.TERMINAL,'NONE') TERMINAL,
L.SID||','||S.SERIAL# KILL,
U1.NAME||'.'||SUBSTR(T1.NAME,1,20) TAB,
DECODE(L.LMODE,1,'NO LOCK',
2,'ROW SHARE',
3,'ROW EXCLUSIVE',
4,'SHARE',
5,'SHARE ROW EXCLUSIVE',
6,'EXCLUSIVE',NULL) LMODE,
DECODE(L.REQUEST,1,'NO LOCK',
2,'ROW SHARE',
3,'ROW EXCLUSIVE',
4,'SHARE',
5,'SHARE ROW EXCLUSIVE',
6,'EXCLUSIVE',NULL) REQUEST
FROM V$LOCK L,
V$SESSION S,
SYS.USER$ U1,
SYS.OBJ$ T1
WHERE L.SID = S.SID
AND T1.OBJ# = DECODE(L.ID2,0,L.ID1,L.ID2)
AND U1.USER# = T1.OWNER#
AND S.TYPE != 'BACKGROUND'
ORDER BY 1,2,5
/
SET LINESIZE 80
SET PAGESIZE 66
COLUMN LMODE HEADING 'LOCK|HELD' FORMAT A4
COLUMN REQUEST HEADING 'LOCK|REQ.' FORMAT A4
COLUMN USERNAME FORMAT A10 HEADING USERNAME
COLUMN TAB FORMAT A30 HEADING TABLE NAME;
COLUMN LADDR HEADING ID1 - ID2 FORMAT A16
COLUMN LOCKT HEADING LOCK|TYPE FORMAT A4
SELECT NVL(S.USERNAME,'INTERNAL') USERNAME,
DECODE(COMMAND,
0,'NONE',DECODE(L.ID2,0,U1.NAME||'.'||SUBSTR(T1.NAME,1,20),
'ROLLBACK SEGMENT')) TAB,
DECODE(L.LMODE,1,'NOLK', 2,' RS ', 3,' RX ',
4,' S ', 5,' SRX', 6,' X ','NONE') LMODE,
DECODE(L.REQUEST,1,'NOLK', 2,' RSH ', 3,' RX ',
4,' S ', 5,' SRX', 6,' X ','NONE') REQUEST,
L.ID1||'-'||L.ID2 LADDR, L.TYPE LOCKT
FROM V$LOCK L, V$SESSION S, SYS.USER$ U1, SYS.OBJ$ T1
WHERE L.SID = S.SID AND T1.OBJ# = DECODE(L.ID2,0,L.ID1,1)
AND U1.USER# = T1.OWNER# AND S.TYPE != 'BACKGROUND'
ORDER BY 1,2,5
/
SET LINES 200
SET PAGESIZE 66
BREAK ON KILL ON SID ON USERNAME ON TERMINAL
COLUMN KILL HEADING 'KILL STRING' FORMAT A13
COLUMN RES HEADING 'RESOURCE TYPE' FORMAT 999
COLUMN ID1 FORMAT 9999990
COLUMN ID2 FORMAT 9999990
COLUMN LOCKING HEADING 'LOCK HELD/LOCK REQUESTED' FORMAT A40
COLUMN LMODE HEADING 'LOCK HELD' FORMAT A20
COLUMN REQUEST HEADING 'LOCK REQUESTED' FORMAT A20
COLUMN SERIAL# FORMAT 99999
COLUMN USERNAME FORMAT A10 HEADING "USERNAME"
COLUMN TERMINAL HEADING TERM FORMAT A6
COLUMN TAB FORMAT A30 HEADING "TABLE NAME"
COLUMN OWNER FORMAT A9
COLUMN LADDR HEADING "ID1 - ID2" FORMAT A18
COLUMN LOCKT HEADING "LOCK TYPE" FORMAT A40
COLUMN COMMAND FORMAT A25
COLUMN SID FORMAT 990
SELECT
NVL(S.USERNAME,'INTERNAL') USERNAME,
L.SID,
NVL(S.TERMINAL,'NONE') TERMINAL,
DECODE(COMMAND,
0,'NONE',DECODE(L.ID2,0,U1.NAME||'.'||SUBSTR(T1.NAME,1,20),'NONE')) TAB,
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,
DECODE(L.LMODE,1,'NO LOCK',
2,'ROW SHARE',
3,'ROW EXCLUSIVE',
4,'SHARE',
5,'SHARE ROW EXCLUSIVE',
6,'EXCLUSIVE','NONE') LMODE,
DECODE(L.REQUEST,1,'NO LOCK',
2,'ROW SHARE',
3,'ROW EXCLUSIVE',
4,'SHARE',
5,'SHARE ROW EXCLUSIVE',
6,'EXCLUSIVE','NONE') REQUEST,
L.ID1||'-'||L.ID2 LADDR,
L.TYPE||' - '||
DECODE(L.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 OPERATIONS 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 NUMBER 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(L.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)','????') LOCKT
FROM V$LOCK L,
V$SESSION S,
SYS.USER$ U1,
SYS.OBJ$ T1
WHERE L.SID = S.SID
AND T1.OBJ# = DECODE(L.ID2,0,L.ID1,1)
AND U1.USER# = T1.OWNER#
AND S.TYPE != 'BACKGROUND'
ORDER BY 1,2,5
/
BLOCKING SESSIONS ON RAC DATABASE:
SELECT DISTINCT S1.USERNAME || '@' || S1.MACHINE
|| ' ( INST=' || S1.INST_ID || ' SID=' || S1.SID || ' ) IS BLOCKING '
|| S2.USERNAME || '@' || S2.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S2.SID || ' ) ' AS BLOCKING_STATUS
FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2
WHERE S1.SID=L1.SID AND S2.SID=L2.SID
AND S1.INST_ID=L1.INST_ID AND S2.INST_ID=L2.INST_ID
AND L1.BLOCK > 0 AND L2.REQUEST > 0
AND L1.ID1 = L2.ID1 AND L1.ID2 = L2.ID2;
SELECT 'ORACLE SESSION WITH USERNAME'|| BLOCKER.USERNAME || ' AND SESSION ID '||BLOCKER.SID
||' IS BLOCKING ' || BLOCKED.USERNAME ||' WITH SESSION ID '|| BLOCKED.SID BLOCK_DESCRIPTION
FROM GV$LOCK LOCKER, GV$SESSION BLOCKER, GV$LOCK LOCKED, GV$SESSION BLOCKED
WHERE BLOCKER.SID=LOCKER.SID AND BLOCKED.SID=LOCKED.SID
AND LOCKER.BLOCK=1
AND LOCKER.ID1 = LOCKED.ID1
AND LOCKER.ID2 = LOCKED.ID2
AND LOCKER.INST_ID = BLOCKER.INST_ID
AND LOCKED.INST_ID = BLOCKED.INST_ID;
BLOCKER INFORMATION:
SELECT S1.USERNAME || '@' || S1.MACHINE || ' ( SID=' || S1.SID || ' ) IS BLOCKING ' || S2.USERNAME || '@' || S2.MACHINE || ' ( SID=' || S2.SID || ' ) ' AS BLOCKING_STATUS
FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2
WHERE S1.SID=L1.SID AND S2.SID=L2.SID
AND L1.BLOCK=1 AND L2.REQUEST > 0
AND L1.ID1 = L2.ID1
AND L2.ID2 = L2.ID2 ;
COMMAND TO KILL THE SESSION:
SELECT DISTINCT 'ALTER SYSTEM KILL SESSION '''||S1.SID||','||S1.SERIAL#||',@'||S1.INST_ID||''' IMMEDIATE;'
FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2
WHERE S1.SID=L1.SID AND S2.SID=L2.SID
AND L1.BLOCK=1 AND L2.REQUEST > 0
AND L1.ID1 = L2.ID1
AND L2.ID2 = L2.ID2
ORDER BY 1;
ALTER SYSTEM KILL SESSION 'sid, serial#';
ALTER SYSTEM KILL SESSION 'sid, serial#' immediate;
ALTER SYSTEM KILL SESSION 'sid, serial#,@<instance_id>';
SESSION DETAILS FOR ANY CONNECTION FROM DEVELOPER TOOL:
SET LINESIZE 200
COL MACHINE FORMAT A30
COL OSUSER FORMAT A10
COL SCHEMANAME FORMAT A10
COL MODULE FORMAT A20
SELECT TO_CHAR(LOGON_TIME,'DD/MM/YYYY HH24:MI:SS'),OSUSER,STATUS,SCHEMANAME,MACHINE,SID,MODULE,INST_ID FROM GV$SESSION WHERE TYPE !='BACKGROUND' AND MODULE LIKE '%DEVELOPER' ORDER BY LOGON_TIME ASC;
SESSION DETAILS:
SELECT P.SPID, SUBSTR(S.SID,1,6) SID, SUBSTR(S.SERIAL#,1,8) SERIAL , S.STATUS STATUS,
SUBSTR(S.USERNAME,1,10) USERNAME , SUBSTR(S.OSUSER,1,10) OSUSER,
TO_CHAR(S.LOGON_TIME,'DD-MM-YYYY HH24:MI') LOGON_TIME, S.PROGRAM PROGRAM
FROM GV$SESSION S , GV$PROCESS P
WHERE S.PADDR = P.ADDR
AND S.SID = &SID;
SUBSTR(S.USERNAME,1,10) USERNAME , SUBSTR(S.OSUSER,1,10) OSUSER,
TO_CHAR(S.LOGON_TIME,'DD-MM-YYYY HH24:MI') LOGON_TIME, S.PROGRAM PROGRAM
FROM GV$SESSION S , GV$PROCESS P
WHERE S.PADDR = P.ADDR
AND S.SID = &SID;
SELECT P.SPID, S.PROCESS CPID, SUBSTR(S.SID,1,6) SID, SUBSTR(S.SERIAL#,1,8) SERIAL , S.STATUS STATUS,
SUBSTR(S.USERNAME,1,10) USERNAME , SUBSTR(S.OSUSER,1,10) OSUSER,
TO_CHAR(S.LOGON_TIME,'DD-MM-YYYY HH24:MI') LOGON_TIME, S.PROGRAM PROGRAM
FROM GV$SESSION S , GV$PROCESS P
WHERE S.PADDR = P.ADDR
AND S.PROGRAM LIKE '%SQLPLUS%';
SELECT P.SPID, S.PROCESS CPID, SUBSTR(S.SID,1,6) SID, SUBSTR(S.SERIAL#,1,8) SERIAL , S.STATUS STATUS,
SUBSTR(S.USERNAME,1,10) USERNAME , SUBSTR(S.OSUSER,1,10) OSUSER,
TO_CHAR(S.LOGON_TIME,'DD-MM-YYYY HH24:MI') LOGON_TIME,
ROUND(S.LAST_CALL_ET/60) IDLE_MIN, S.PROGRAM PROGRAM
FROM GV$SESSION S , GV$PROCESS P
WHERE S.PADDR = P.ADDR
AND S.PROGRAM LIKE '%SQLPLUS%' ;
SELECT P.SPID, S.PROCESS CPID, SUBSTR(S.SID,1,6)||','||SUBSTR(S.SERIAL#,1,8) SIDSERIAL , S.STATUS STATUS,
TO_CHAR(S.LOGON_TIME,'DD-MM-YYYY HH24:MI') LOGON_TIME, ROUND(S.LAST_CALL_ET/60) IDLE_MIN,
SUBSTR(S.USERNAME,1,10) USERNAME , SUBSTR(S.OSUSER,1,10) OSUSER,
S.PROGRAM PROGRAM
FROM GV$SESSION S , GV$PROCESS P
WHERE S.PADDR = P.ADDR
AND S.USERNAME IS NOT NULL
AND S.USERNAME NOT IN ('SYS')
ORDER BY IDLE_MIN DESC;
LONG RUNNING SESSION DETAILS:
COLUMN USERNAME FORMAT 'A10'
COLUMN OSUSER FORMAT 'A10'
COLUMN MODULE FORMAT 'A16'
COLUMN PROGRAM_NAME FORMAT 'A20'
COLUMN PROGRAM FORMAT 'A20'
COLUMN MACHINE FORMAT 'A20'
COLUMN ACTION FORMAT 'A20'
COLUMN SID FORMAT '9999'
COLUMN SERIAL# FORMAT '99999'
COLUMN SPID FORMAT '99999'
SET LINESIZE 200
SET PAGESIZE 30
COLUMN OSUSER FORMAT 'A10'
COLUMN MODULE FORMAT 'A16'
COLUMN PROGRAM_NAME FORMAT 'A20'
COLUMN PROGRAM FORMAT 'A20'
COLUMN MACHINE FORMAT 'A20'
COLUMN ACTION FORMAT 'A20'
COLUMN SID FORMAT '9999'
COLUMN SERIAL# FORMAT '99999'
COLUMN SPID FORMAT '99999'
SET LINESIZE 200
SET PAGESIZE 30
SELECT
A.SID,A.SERIAL#,A.USERNAME,A.OSUSER,C.START_TIME,
B.SPID,A.STATUS,A.MACHINE,
A.ACTION,A.MODULE,A.PROGRAM
FROM
GV$SESSION A, GV$PROCESS B, GV$TRANSACTION C,
GV$SQLAREA S
WHERE
A.PADDR = B.ADDR
AND A.SADDR = C.SES_ADDR
AND A.SQL_ADDRESS = S.ADDRESS (+)
AND TO_DATE(C.START_TIME,'MM/DD/YY HH24:MI:SS') <= SYSDATE - (60/1440) -- RUNNING FOR 60 MINUTES
ORDER BY C.START_TIME;
SELECT 'SID:'||S.SID||', SERIAL#:'||S.SERIAL#||', USERNAME:'||S.USERNAME||', MACHINE:'||S.MACHINE||
', PROGRAM:'||S.PROGRAM||', HASHVALUE:'||S.SQL_HASH_VALUE||', SQL TEXT:'||NVL(SUBSTR(SQL.SQL_TEXT,1,40),'UNKNOWN SQL'), LAST_CALL_ET
FROM V$SESSION S
LEFT OUTER JOIN V$SQL SQL ON SQL.HASH_VALUE=S.SQL_HASH_VALUE AND SQL.ADDRESS=S.SQL_ADDRESS
WHERE S.STATUS='ACTIVE'
AND S.TYPE <> 'BACKGROUND'
AND LAST_CALL_ET >= 3600;
SESSION SID DETAILS:
COL EVENT FOR A30
SELECT ' SID : '||V.SID || CHR(10)||
' SERIAL NUMBER : '||V.SERIAL# || CHR(10) ||
' ORACLE USER NAME : '||V.USERNAME || CHR(10) ||
' CLIENT OS USER NAME : '||V.OSUSER || CHR(10) ||
' CLIENT PROCESS ID : '||V.PROCESS || CHR(10) ||
' CLIENT MACHINE NAME : '||V.MACHINE || CHR(10) ||
' ORACLE PID : '||P.PID || CHR(10) ||
' OS PROCESS ID(SPID) : '||P.SPID || CHR(10) ||
' SESSION''S STATUS : '||V.STATUS || CHR(10) ||
' LOGON TIME : '||TO_CHAR(V.LOGON_TIME, 'MM/DD HH24:MIPM') || CHR(10) ||
' PROGRAM NAME : '||V.PROGRAM || CHR(10)
FROM V$SESSION V, V$PROCESS P
WHERE V.PADDR = P.ADDR
AND V.SERIAL# > 1
AND P.BACKGROUND IS NULL
AND P.USERNAME IS NOT NULL
AND SID = &SID_NUMBER
ORDER BY LOGON_TIME, V.STATUS, 1
PROMPT SQL STATEMENT
PROMPT --------------
SELECT SQL_TEXT
FROM V$SQLTEXT , V$SESSION
WHERE V$SQLTEXT.ADDRESS = V$SESSION.SQL_ADDRESS
AND SID = &SID_NUMBER
ORDER BY PIECE
/
PROMPT
PROMPT EVENT WAIT INFORMATION
PROMPT ----------------------
SELECT ' SID '|| &SID_NUMBER ||' IS WAITING ON EVENT : ' || X.EVENT || CHR(10) ||
' P1 TEXT : ' || X.P1TEXT || CHR(10) ||
' P1 VALUE : ' || X.P1 || CHR(10) ||
' P2 TEXT : ' || X.P2TEXT || CHR(10) ||
' P2 VALUE : ' || X.P2 || CHR(10) ||
' P3 TEXT : ' || X.P3TEXT || CHR(10) ||
' P3 VALUE : ' || X.P3
FROM V$SESSION_WAIT X
WHERE X.SID= &SID_NUMBER
/
PROMPT
PROMPT SESSION STATISTICS
PROMPT ------------------
SELECT ' '|| B.NAME ||' : '||DECODE(B.NAME, 'REDO SIZE', ROUND(A.VALUE/1024/1024,2)||' M', A.VALUE)
FROM V$SESSION S, V$SESSTAT A, V$STATNAME B
WHERE A.STATISTIC# = B.STATISTIC#
AND NAME IN ('REDO SIZE', 'PARSE COUNT (TOTAL)', 'PARSE COUNT (HARD)', 'USER COMMITS')
AND S.SID = &SID_NUMBER
AND A.SID = &SID_NUMBER
--ORDER BY B.NAME
ORDER BY DECODE(B.NAME, 'REDO SIZE', 1, 2), B.NAME
/
COLUMN USERNAME FORMAT A10
COLUMN STATUS FORMAT A8
COLUMN RBS_NAME FORMAT A10
PROMPT
PROMPT TRANSACTION AND ROLLBACK INFORMATION
PROMPT ------------------------------------
SELECT ' ROLLBACK USED : '||T.USED_UBLK*8192/1024/1024 ||' M' || CHR(10) ||
' ROLLBACK RECORDS : '||T.USED_UREC || CHR(10)||
' ROLLBACK SEGMENT NUMBER : '||T.XIDUSN || CHR(10)||
' ROLLBACK SEGMENT NAME : '||R.NAME || CHR(10)||
' LOGICAL IOS : '||T.LOG_IO || CHR(10)||
' PHYSICAL IOS : '||T.PHY_IO || CHR(10)||
' RBS STARTNG EXTENT ID : '||T.START_UEXT || CHR(10)||
' TRANSACTION START TIME : '||T.START_TIME || CHR(10)||
' TRANSACTION_STATUS : '||T.STATUS
FROM V$TRANSACTION T, V$SESSION S, V$ROLLNAME R
WHERE T.ADDR = S.TADDR
AND R.USN = T.XIDUSN
AND S.SID = &SID_NUMBER
/
PROMPT
PROMPT SORT INFORMATION
PROMPT ----------------
COLUMN USERNAME FORMAT A20
COLUMN USER FORMAT A20
COLUMN TABLESPACE FORMAT A20
SELECT ' SORT SPACE USED(8K BLOCK SIZE IS ASSSUMED : '||U.BLOCKS/1024*8 ||' M' || CHR(10) ||
' SORTING TABLESPACE : '||U.TABLESPACE || CHR(10)||
' SORT TABLESPACE TYPE : '||U.CONTENTS || CHR(10)||
' TOTAL EXTENTS USED FOR SORTING : '||U.EXTENTS
FROM V$SESSION S, V$SORT_USAGE U
WHERE S.SADDR = U.SESSION_ADDR
AND S.SID = &SID_NUMBER
/
V$LOCKED_OBJECT: THIS VIEW LISTS ALL LOCKS ACQUIRED BY EVERY TRANSACTION ON THE SYSTEM:
SET LINESIZE 130
SET PAGES 100
COL USERNAME FORMAT A20
COL SESS_ID FORMAT A10
OL OBJECT FORMAT A25
COL MODE_HELD FORMAT A10
SELECT ORACLE_USERNAME || ' (' || S.OSUSER || ')' USERNAME
, S.SID || ',' || S.SERIAL# SESS_ID
, OWNER || '.' || OBJECT_NAME OBJECT
, OBJECT_TYPE
, decode( l.block
, 0, 'Not Blocking'
, 1, 'Blocking'
, 2, 'Global') status
, decode(v.locked_mode
, 0, 'None'
, 1, 'Null'
, 2, 'Row-S (SS)'
, 3, 'Row-X (SX)'
, 4, 'Share'
, 5, 'S/Row-X (SSX)'
, 6, 'Exclusive', TO_CHAR(lmode)) mode_held
FROM V$LOCKED_OBJECT V
, DBA_OBJECTS D
, V$LOCK L
, V$SESSION S
WHERE V.OBJECT_ID = D.OBJECT_ID
AND V.OBJECT_ID = L.ID1
AND V.SESSION_ID = S.SID
ORDER BY ORACLE_USERNAME
, SESSION_ID
/
SELECT S.INST_ID INSTANCE_ID, S.SID, S.SERIAL#, P.USERNAME OS_PROCESS_OWNER, P.PID, P.SPID OS_PROCESS_ID,
L.SESSION_ID, L.ORACLE_USERNAME, L.OS_USER_NAME, L.PROCESS,
DECODE (LOCKED_MODE,
0, 'NONE',
1, 'NULL',
2, 'ROW-SHARE',
3, 'ROW-EXCLUSIVE.',
4, 'SHARE',
5, 'S/ROW-EXCLUSIVE',
6, 'EXCLUSIVE'
) AS LOCK_TYPE,
O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE, S.PROGRAM, S.LOGON_TIME, S.STATUS SESSION_STATUS,
O.STATUS OBJECT_STATUS
FROM GV$LOCKED_OBJECT L, DBA_OBJECTS O, GV$SESSION S, GV$PROCESS P
WHERE (L.OBJECT_ID = O.OBJECT_ID AND L.SESSION_ID = S.SID)
AND S.PADDR = P.ADDR
AND L.INST_ID = P.INST_ID
AND S.INST_ID = P.INST_ID
ORDER BY OBJECT_NAME;
BLOCKING SESSION FOR LAST 3 HOURS:
SELECT DISTINCT A.SQL_ID ,A.INST_ID,A.BLOCKING_SESSION,A.BLOCKING_SESSION_SERIAL#,A.USER_ID,S.SQL_TEXT,A.MODULE
FROM GV$ACTIVE_SESSION_HISTORY A ,GV$SQL S
WHERE A.SQL_ID=S.SQL_ID
AND BLOCKING_SESSION IS NOT NULL
AND A.USER_ID <> 0
AND A.SAMPLE_TIME > SYSDATE - 3/24;
BLOCKING SESSION FOR LAST 7 DAYS:
SELECT * FROM (
SELECT A.SQL_ID ,
COUNT(*) OVER (PARTITION BY A.BLOCKING_SESSION,A.USER_ID ,A.PROGRAM) CPT,
ROW_NUMBER() OVER (PARTITION BY A.BLOCKING_SESSION,A.USER_ID ,A.PROGRAM
ORDER BY BLOCKING_SESSION,A.USER_ID ,A.PROGRAM ) RN,
A.BLOCKING_SESSION,A.USER_ID ,A.PROGRAM, S.SQL_TEXT
FROM SYS.WRH$_ACTIVE_SESSION_HISTORY A ,SYS.WRH$_SQLTEXT S
WHERE A.SQL_ID=S.SQL_ID
AND BLOCKING_SESSION_SERIAL# <> 0
AND A.USER_ID <> 0
AND A.SAMPLE_TIME > SYSDATE -1) WHERE RN = 1;
SESSION DETAILS ASSOCIATED WITH ORACLE SID :
SET HEAD OFF
SET VERIFY OFF
SET ECHO OFF
SET PAGES 1500
SET LINESIZE 100
SET LINES 120
PROMPT
PROMPT DETAILS OF SID / SPID / CLIENT PID
PROMPT ==================================
SELECT /*+ CHOOSE*/
'SESSION ID.............................................: '||S.SID,
'SERIAL NUM..............................................: '||S.SERIAL#,
'USER NAME ..............................................: '||S.USERNAME,
'SESSION STATUS .........................................: '||S.STATUS,
'CLIENT PROCESS ID ON CLIENT MACHINE ....................: '||'*'||S.PROCESS||'*' CLIENT,
'SERVER PROCESS ID ......................................: '||P.SPID SERVER,
'SQL_ADDRESS ............................................: '||S.SQL_ADDRESS,
'SQL_HASH_VALUE .........................................: '||S.SQL_HASH_VALUE,
'SCHEMA NAME ..... ......................................: '||S.SCHEMANAME,
'PROGRAM ...............................................: '||S.PROGRAM,
'MODULE .................................................: '|| S.MODULE,
'ACTION .................................................: '||S.ACTION,
'TERMINAL ...............................................: '||S.TERMINAL,
'CLIENT MACHINE .........................................: '||S.MACHINE,
'LAST_CALL_ET ...........................................: '||S.LAST_CALL_ET,
'S.LAST_CALL_ET/3600 ....................................: '||S.LAST_CALL_ET/3600
FROM V$SESSION S, V$PROCESS P
WHERE P.ADDR=S.PADDR and
S.SID=NVL('&SID',S.SID)
/
No comments:
Post a Comment