Oracle Database Administrator: December 2020

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

- 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;

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

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)

Oracle Database Administrator

Thursday, December 31, 2020

Performing Database health checks

To Find out Blocking Sessions & Session Details