Oracle session is doing what ..???
Oracle session command
set linesize 300 pagesize 200 col tab for a20 col killstmt for a35 col laddr for a12 col lockt for a30 col command for a12 col terminal for a15 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, s.last_call_et, Decode (Command, 0,'UNKNOWN', 1,'CREATE TABLE', 2,'INSERT', 3,'SELECT', 4,'CREATE CLUSTER', 5,'ALTER CLUSTER', 6,'UPDATE', 7,'DELETE', 8,'DROP CLUSTER', 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 OBJECT', 18,'REVOKE OBJECT', 19,'CREATE SYNONYM', 20,'DROP SYNONYM', 21,'CREATE VIEW', 22,'DROP VIEW', 23,'VALIDATE INDEX', 24,'CREATE PROCEDURE', 25,'ALTER PROCEDURE', 26,'LOCK', 27,'NO-OP', 28,'RENAME', 29,'COMMENT', 30,'AUDIT OBJECT', 31,'NOAUDIT OBJECT', 32,'CREATE DATABASE LINK', 33,'DROP DATABASE LINK', 34,'CREATE DATABASE', 35,'ALTER DATABASE', 36,'CREATE ROLLBACK SEG', 37,'ALTER ROLLBACK SEG', 38,'DROP ROLLBACK SEG', 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', 50,'EXPLAIN', 51,'CREATE USER', 52,'CREATE ROLE', 53,'DROP USER', 54,'DROP ROLE', 55,'SET ROLE', 56,'CREATE SCHEMA', 57,'CREATE CONTROL FILE', 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', 70,'ALTER RESOURCE COST', 71,'CREATE MATERIALIZED VIEW LOG', 72,'ALTER MATERIALIZED VIEW LOG', 73,'DROP MATERIALIZED VIEW LOG', 74,'CREATE MATERIALIZED VIEW', 75,'ALTER MATERIALIZED VIEW', 76,'DROP MATERIALIZED VIEW', 77,'CREATE TYPE', 78,'DROP TYPE', 79,'ALTER ROLE', 80,'ALTER TYPE', 81,'CREATE TYPE BODY', 82,'ALTER TYPE BODY', 83,'DROP TYPE BODY', 84,'DROP LIBRARY', 85,'TRUNCATE TABLE', 86,'TRUNCATE CLUSTER', 88,'ALTER VIEW', 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', 100,'LOGON', 101,'LOGOFF', 102,'LOGOFF BY CLEANUP', 103,'SESSION REC', 104,'SYSTEM AUDIT', 105,'SYSTEM NOAUDIT', 106,'AUDIT DEFAULT', 107,'NOAUDIT DEFAULT', 108,'SYSTEM GRANT', 109,'SYSTEM REVOKE', 110,'CREATE PUBLIC SYNONYM', 111,'DROP PUBLIC SYNONYM', 112,'CREATE PUBLIC DATABASE LINK', 113,'DROP PUBLIC DATABASE LINK', 114,'GRANT ROLE', 115,'REVOKE ROLE', 116,'EXECUTE PROCEDURE', 117,'USER COMMENT', 118,'ENABLE TRIGGER', 119,'DISABLE TRIGGER', 120,'ENABLE ALL TRIGGERS', 121,'DISABLE ALL TRIGGERS', 122,'NETWORK ERROR', 123,'EXECUTE TYPE', 128,'FLASHBACK', 129,'CREATE SESSION', 130,'ALTER MINING MODEL', 131,'SELECT MINING MODEL', 133,'CREATE MINING MODEL', 134,'ALTER PUBLIC SYNONYM', 135,'DIRECTORY EXECUTE', 136,'SQL*LOADER DIRECT PATH LOAD', 137,'DATAPUMP DIRECT PATH UNLOAD', 138,'DATABASE STARTUP', 139,'DATABASE SHUTDOWN', 140,'CREATE SQL TXLN PROFILE', 141,'ALTER SQL TXLN PROFILE', 142,'USE SQL TXLN PROFILE', 143,'DROP SQL TXLN PROFILE', 144,'CREATE MEASURE FOLDER', 145,'ALTER MEASURE FOLDER', 146,'DROP MEASURE FOLDER', 147,'CREATE CUBE BUILD PROCESS', 148,'ALTER CUBE BUILD PROCESS', 149,'DROP CUBE BUILD PROCESS', 150,'CREATE CUBE', 151,'ALTER CUBE', 152,'DROP CUBE', 153,'CREATE CUBE DIMENSION', 154,'ALTER CUBE DIMENSION', 155,'DROP CUBE DIMENSION', 157,'CREATE DIRECTORY', 158,'DROP DIRECTORY', 159,'CREATE LIBRARY', 160,'CREATE JAVA', 161,'ALTER JAVA', 162,'DROP JAVA', 163,'CREATE OPERATOR', 164,'CREATE INDEXTYPE', 165,'DROP INDEXTYPE', 166,'ALTER INDEXTYPE', 167,'DROP OPERATOR', 168,'ASSOCIATE STATISTICS', 169,'DISASSOCIATE STATISTICS', 170,'CALL METHOD', 171,'CREATE SUMMARY', 172,'ALTER SUMMARY', 173,'DROP SUMMARY', 174,'CREATE DIMENSION', 175,'ALTER DIMENSION', 176,'DROP DIMENSION', 177,'CREATE CONTEXT', 178,'DROP CONTEXT', 179,'ALTER OUTLINE', 180,'CREATE OUTLINE', 181,'DROP OUTLINE', 182,'UPDATE INDEXES', 183,'ALTER OPERATOR', 190,'PASSWORD CHANGE', 192,'ALTER SYNONYM', 197,'PURGE USER_RECYCLEBIN', 198,'PURGE DBA_RECYCLEBIN', 199,'PURGE TABLESPACE', 200,'PURGE TABLE', 201,'PURGE INDEX', 202,'UNDROP OBJECT', 204,'FLASHBACK DATABASE', 205,'FLASHBACK TABLE', 206,'CREATE RESTORE POINT', 207,'DROP RESTORE POINT', 208,'PROXY AUTHENTICATION ONLY', 209,'DECLARE REWRITE EQUIVALENCE', 210,'ALTER REWRITE EQUIVALENCE', 211,'DROP REWRITE EQUIVALENCE', 212,'CREATE EDITION', 213,'ALTER EDITION', 214,'DROP EDITION', 215,'DROP ASSEMBLY', 216,'CREATE ASSEMBLY', 217,'ALTER ASSEMBLY', 218,'CREATE FLASHBACK ARCHIVE', 219,'ALTER FLASHBACK ARCHIVE', 220,'DROP FLASHBACK ARCHIVE', 225,'ALTER DATABASE LINK', 226,'CREATE PLUGGABLE DATABASE', 227,'ALTER PLUGGABLE DATABASE', 228,'DROP PLUGGABLE DATABASE', 229,'CREATE AUDIT POLICY', 230,'ALTER AUDIT POLICY', 231,'DROP AUDIT POLICY', 232,'CODE-BASED GRANT', 233,'CODE-BASED REVOKE', 238,'ADMINISTER KEY MANAGEMENT', 239,'CREATE MATERIALIZED ZONEMAP', 240,'ALTER MATERIALIZED ZONEMAP', 241,'DROP MATERIALIZED ZONEMAP', 305,'ALTER PUBLIC DATABASE LINK', Command || ' - ???' ) Command ,s.sql_id ,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, ' alter system kill Session ' || ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||''''||' immediate;' killstmt from gv$lock l, gv$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' and s.inst_id =l.inst_id order by s.sid; -- ========================================== set linesize 300 pagesize 400 col PROGRAM for a50 col killstmt for a55 col killstmt for a15 col sql_text for a50 wrap col NAME for a25 col USERNAME for a15 col COMMAND for a15 col PROGRAM for a20 col kill for a15 select distinct -- ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill , substr(s.username,1,20) username,substr(s.program,1,50) program, s.sql_id,s.prev_sql_id,n.NAME, decode(s.command, 0,'UNKNOWN', 1,'CREATE TABLE', 2,'INSERT', 3,'SELECT', 4,'CREATE CLUSTER', 5,'ALTER CLUSTER', 6,'UPDATE', 7,'DELETE', 8,'DROP CLUSTER', 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 OBJECT', 18,'REVOKE OBJECT', 19,'CREATE SYNONYM', 20,'DROP SYNONYM', 21,'CREATE VIEW', 22,'DROP VIEW', 23,'VALIDATE INDEX', 24,'CREATE PROCEDURE', 25,'ALTER PROCEDURE', 26,'LOCK', 27,'NO-OP', 28,'RENAME', 29,'COMMENT', 30,'AUDIT OBJECT', 31,'NOAUDIT OBJECT', 32,'CREATE DATABASE LINK', 33,'DROP DATABASE LINK', 34,'CREATE DATABASE', 35,'ALTER DATABASE', 36,'CREATE ROLLBACK SEG', 37,'ALTER ROLLBACK SEG', 38,'DROP ROLLBACK SEG', 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', 50,'EXPLAIN', 51,'CREATE USER', 52,'CREATE ROLE', 53,'DROP USER', 54,'DROP ROLE', 55,'SET ROLE', 56,'CREATE SCHEMA', 57,'CREATE CONTROL FILE', 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', 70,'ALTER RESOURCE COST', 71,'CREATE MATERIALIZED VIEW LOG', 72,'ALTER MATERIALIZED VIEW LOG', 73,'DROP MATERIALIZED VIEW LOG', 74,'CREATE MATERIALIZED VIEW', 75,'ALTER MATERIALIZED VIEW', 76,'DROP MATERIALIZED VIEW', 77,'CREATE TYPE', 78,'DROP TYPE', 79,'ALTER ROLE', 80,'ALTER TYPE', 81,'CREATE TYPE BODY', 82,'ALTER TYPE BODY', 83,'DROP TYPE BODY', 84,'DROP LIBRARY', 85,'TRUNCATE TABLE', 86,'TRUNCATE CLUSTER', 88,'ALTER VIEW', 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', 100,'LOGON', 101,'LOGOFF', 102,'LOGOFF BY CLEANUP', 103,'SESSION REC', 104,'SYSTEM AUDIT', 105,'SYSTEM NOAUDIT', 106,'AUDIT DEFAULT', 107,'NOAUDIT DEFAULT', 108,'SYSTEM GRANT', 109,'SYSTEM REVOKE', 110,'CREATE PUBLIC SYNONYM', 111,'DROP PUBLIC SYNONYM', 112,'CREATE PUBLIC DATABASE LINK', 113,'DROP PUBLIC DATABASE LINK', 114,'GRANT ROLE', 115,'REVOKE ROLE', 116,'EXECUTE PROCEDURE', 117,'USER COMMENT', 118,'ENABLE TRIGGER', 119,'DISABLE TRIGGER', 120,'ENABLE ALL TRIGGERS', 121,'DISABLE ALL TRIGGERS', 122,'NETWORK ERROR', 123,'EXECUTE TYPE', 128,'FLASHBACK', 129,'CREATE SESSION', 130,'ALTER MINING MODEL', 131,'SELECT MINING MODEL', 133,'CREATE MINING MODEL', 134,'ALTER PUBLIC SYNONYM', 135,'DIRECTORY EXECUTE', 136,'SQL*LOADER DIRECT PATH LOAD', 137,'DATAPUMP DIRECT PATH UNLOAD', 138,'DATABASE STARTUP', 139,'DATABASE SHUTDOWN', 140,'CREATE SQL TXLN PROFILE', 141,'ALTER SQL TXLN PROFILE', 142,'USE SQL TXLN PROFILE', 143,'DROP SQL TXLN PROFILE', 144,'CREATE MEASURE FOLDER', 145,'ALTER MEASURE FOLDER', 146,'DROP MEASURE FOLDER', 147,'CREATE CUBE BUILD PROCESS', 148,'ALTER CUBE BUILD PROCESS', 149,'DROP CUBE BUILD PROCESS', 150,'CREATE CUBE', 151,'ALTER CUBE', 152,'DROP CUBE', 153,'CREATE CUBE DIMENSION', 154,'ALTER CUBE DIMENSION', 155,'DROP CUBE DIMENSION', 157,'CREATE DIRECTORY', 158,'DROP DIRECTORY', 159,'CREATE LIBRARY', 160,'CREATE JAVA', 161,'ALTER JAVA', 162,'DROP JAVA', 163,'CREATE OPERATOR', 164,'CREATE INDEXTYPE', 165,'DROP INDEXTYPE', 166,'ALTER INDEXTYPE', 167,'DROP OPERATOR', 168,'ASSOCIATE STATISTICS', 169,'DISASSOCIATE STATISTICS', 170,'CALL METHOD', 171,'CREATE SUMMARY', 172,'ALTER SUMMARY', 173,'DROP SUMMARY', 174,'CREATE DIMENSION', 175,'ALTER DIMENSION', 176,'DROP DIMENSION', 177,'CREATE CONTEXT', 178,'DROP CONTEXT', 179,'ALTER OUTLINE', 180,'CREATE OUTLINE', 181,'DROP OUTLINE', 182,'UPDATE INDEXES', 183,'ALTER OPERATOR', 190,'PASSWORD CHANGE', 192,'ALTER SYNONYM', 197,'PURGE USER_RECYCLEBIN', 198,'PURGE DBA_RECYCLEBIN', 199,'PURGE TABLESPACE', 200,'PURGE TABLE', 201,'PURGE INDEX', 202,'UNDROP OBJECT', 204,'FLASHBACK DATABASE', 205,'FLASHBACK TABLE', 206,'CREATE RESTORE POINT', 207,'DROP RESTORE POINT', 208,'PROXY AUTHENTICATION ONLY', 209,'DECLARE REWRITE EQUIVALENCE', 210,'ALTER REWRITE EQUIVALENCE', 211,'DROP REWRITE EQUIVALENCE', 212,'CREATE EDITION', 213,'ALTER EDITION', 214,'DROP EDITION', 215,'DROP ASSEMBLY', 216,'CREATE ASSEMBLY', 217,'ALTER ASSEMBLY', 218,'CREATE FLASHBACK ARCHIVE', 219,'ALTER FLASHBACK ARCHIVE', 220,'DROP FLASHBACK ARCHIVE', 225,'ALTER DATABASE LINK', 226,'CREATE PLUGGABLE DATABASE', 227,'ALTER PLUGGABLE DATABASE', 228,'DROP PLUGGABLE DATABASE', 229,'CREATE AUDIT POLICY', 230,'ALTER AUDIT POLICY', 231,'DROP AUDIT POLICY', 232,'CODE-BASED GRANT', 233,'CODE-BASED REVOKE', 238,'ADMINISTER KEY MANAGEMENT', 239,'CREATE MATERIALIZED ZONEMAP', 240,'ALTER MATERIALIZED ZONEMAP', 241,'DROP MATERIALIZED ZONEMAP', 305,'ALTER PUBLIC DATABASE LINK', s.command||': Other') command , --' alter system kill Session ' || ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||''''||' immediate;' killstmt ''''||s.sid||','||s.serial#||'@'||s.inst_id||'''' killstmt , sq.sql_text sql_text,state ,event,SECONDS_IN_WAIT from gv$session s,gv$process p,gv$transaction t,gv$rollstat r,v$rollname n,gv$sql sq where s.paddr = p.addr and s.taddr = t.addr (+) and t.xidusn = r.usn (+) and r.usn = n.usn (+) and s.inst_id =s.inst_id and p.inst_id =s.inst_id and sq.inst_id=s.inst_id and s.type != 'BACKGROUND' and s.COMMAND!=0 and s.status ='INACTIVE' and ( (sq.sql_id = s.sql_id and sq.child_number = s.sql_child_number) or (sq.sql_id = s.prev_sql_id and sq.child_number = s.prev_child_number) ) and s.USERNAME Not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) order by 1; select * from AUDIT_ACTIONS ;
set lines 300 pagesize 200
break on Kill on sid on username on terminal
col Kill heading 'Kill String' for a15
col res heading 'Resource Type' for 999
col id1 for 9999999
col id2 for 9999999
col locking heading 'Lock Held/Lock Requested' for a40
col lmode heading 'Lock Held' for a15
col request heading 'Lock Requested' for a15
col username for a10 heading "Username"
col terminal heading Term for a12
col tab for a30 heading "Table Name"
col owner for a10
col LAddr heading "ID1 - ID2" for a18
col Lockt heading "Lock Type" for a30
col command for a25
col sid for 9999
col kill for a15
select distinct
''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill ,
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, 'NO COMMAND',
1, 'CREATE TABLE',
2, 'INSERT',
3,'ALTER SYSTEM',
4,'SYSTEM AUDIT',
5,'CREATE SESSION',
6,'ALTER SESSION',
7,'RESTRICTED SESSION',
8,'TABLE',
9,'CLUSTER',
10,'CREATE TABLESPACE',
11,'ALTER TABLESPACE',
12,'MANAGE TABLESPACE',
13,'DROP TABLESPACE',
14,'TABLESPACE',
15,'UNLIMITED TABLESPACE',
16,'USER',
17,'ROLLBACK SEGMENT',
18,'TYPE',
19,'INDEX',
20,'CREATE USER',
21,'BECOME USER',
22,'ALTER USER',
23,'DROP USER',
24,'SYNONYM',
25,'PUBLIC SYNONYM',
26,'VIEW',
27,'SEQUENCE',
28,'DATABASE LINK',
29,'PUBLIC DATABASE LINK',
30,'CREATE ROLLBACK SEGMENT',
31,'ALTER ROLLBACK SEGMENT',
32,'DROP ROLLBACK SEGMENT',
33,'ROLE',
34,'DIMENSION',
35,'PROCEDURE',
36,'TRIGGER',
37,'PROFILE',
38,'DIRECTORY',
39,'MATERIALIZED VIEW',
40,'CREATE TABLE',
41,'CREATE ANY TABLE',
42,'ALTER ANY TABLE',
43,'BACKUP ANY TABLE',
44,'DROP ANY TABLE',
45,'LOCK ANY TABLE',
46,'COMMENT ANY TABLE',
47,'SELECT ANY TABLE',
48,'INSERT ANY TABLE',
49,'UPDATE ANY TABLE',
50,'DELETE ANY TABLE',
51,'OUTLINE',
52,'MINING MODEL',
54,'ALTER TABLE',
57,'LOCK TABLE',
58,'COMMENT TABLE',
60,'CREATE CLUSTER',
61,'CREATE ANY CLUSTER',
62,'ALTER ANY CLUSTER',
63,'DROP ANY CLUSTER',
65,'SELECT TABLE',
66,'INSERT TABLE',
67,'UPDATE TABLE',
68,'DELETE TABLE',
69,'GRANT TABLE',
71,'CREATE ANY INDEX',
72,'ALTER ANY INDEX',
73,'DROP ANY INDEX',
77,'NOT EXISTS',
78,'ALL STATEMENTS',
80,'CREATE SYNONYM',
81,'CREATE ANY SYNONYM',
82,'DROP ANY SYNONYM',
83,'SYSDBA',
84,'SYSOPER',
85,'CREATE PUBLIC SYNONYM',
86,'DROP PUBLIC SYNONYM',
90,'CREATE VIEW',
91,'CREATE ANY VIEW',
92,'DROP ANY VIEW',
93,'CREATE JAVA SOURCE',
94,'CREATE JAVA CLASS',
95,'CREATE JAVA RESOURCE',
96,'ALTER JAVA SOURCE',
97,'ALTER JAVA CLASS',
98,'ALTER JAVA RESOURCE',
99,'DROP JAVA SOURCE',
100,'DROP JAVA CLASS',
101,'DROP JAVA RESOURCE',
103,'ALTER SEQUENCE',
104,'SELECT SEQUENCE',
105,'CREATE SEQUENCE',
106,'CREATE ANY SEQUENCE',
107,'ALTER ANY SEQUENCE',
108,'DROP ANY SEQUENCE',
109,'SELECT ANY SEQUENCE',
111,'GRANT SEQUENCE',
115,'CREATE DATABASE LINK',
120,'CREATE PUBLIC DATABASE LINK',
121,'DROP PUBLIC DATABASE LINK',
125,'CREATE ROLE',
126,'DROP ANY ROLE',
127,'GRANT ANY ROLE',
128,'ALTER ANY ROLE',
130,'AUDIT ANY',
131,'SYSTEM GRANT',
135,'ALTER DATABASE',
138,'FORCE TRANSACTION',
139,'FORCE ANY TRANSACTION',
140,'CREATE PROCEDURE',
141,'CREATE ANY PROCEDURE',
142,'ALTER ANY PROCEDURE',
143,'DROP ANY PROCEDURE',
144,'EXECUTE ANY PROCEDURE',
146,'EXECUTE PROCEDURE',
147,'GRANT PROCEDURE',
151,'CREATE TRIGGER',
152,'CREATE ANY TRIGGER',
153,'ALTER ANY TRIGGER',
154,'DROP ANY TRIGGER',
160,'CREATE PROFILE',
161,'ALTER PROFILE',
162,'DROP PROFILE',
163,'ALTER RESOURCE COST',
165,'ANALYZE ANY',
167,'GRANT ANY PRIVILEGE',
172,'CREATE MATERIALIZED VIEW',
173,'CREATE ANY MATERIALIZED VIEW',
174,'ALTER ANY MATERIALIZED VIEW',
175,'DROP ANY MATERIALIZED VIEW',
176,'NETWORK',
177,'CREATE ANY DIRECTORY',
178,'DROP ANY DIRECTORY',
179,'GRANT DIRECTORY',
180,'CREATE TYPE',
181,'CREATE ANY TYPE',
182,'ALTER ANY TYPE',
183,'DROP ANY TYPE',
184,'EXECUTE ANY TYPE',
186,'UNDER ANY TYPE',
187,'GRANT TYPE',
188,'CREATE LIBRARY',
189,'CREATE ANY LIBRARY',
190,'ALTER ANY LIBRARY',
191,'DROP ANY LIBRARY',
192,'EXECUTE ANY LIBRARY',
193,'EXECUTE LIBRARY',
200,'CREATE OPERATOR',
201,'CREATE ANY OPERATOR',
202,'ALTER ANY OPERATOR',
203,'DROP ANY OPERATOR',
204,'EXECUTE ANY OPERATOR',
205,'CREATE INDEXTYPE',
206,'CREATE ANY INDEXTYPE',
207,'ALTER ANY INDEXTYPE',
208,'DROP ANY INDEXTYPE',
209,'UNDER ANY VIEW',
210,'QUERY REWRITE',
211,'GLOBAL QUERY REWRITE',
212,'EXECUTE ANY INDEXTYPE',
213,'UNDER ANY TABLE',
214,'CREATE DIMENSION',
215,'CREATE ANY DIMENSION',
216,'ALTER ANY DIMENSION',
217,'DROP ANY DIMENSION',
218,'MANAGE ANY QUEUE',
219,'ENQUEUE ANY QUEUE',
220,'DEQUEUE ANY QUEUE',
221,'CONTEXT',
222,'CREATE ANY CONTEXT',
223,'DROP ANY CONTEXT',
224,'CREATE ANY OUTLINE',
225,'ALTER ANY OUTLINE',
226,'DROP ANY OUTLINE',
227,'ADMINISTER RESOURCE MANAGER',
228,'ADMINISTER DATABASE TRIGGER',
233,'MERGE ANY VIEW',
234,'ON COMMIT REFRESH',
235,'EXEMPT ACCESS POLICY',
236,'RESUMABLE',
237,'SELECT ANY DICTIONARY',
238,'DEBUG CONNECT SESSION',
241,'DEBUG ANY PROCEDURE',
242,'DEBUG PROCEDURE',
243,'FLASHBACK ANY TABLE',
244,'GRANT ANY OBJECT PRIVILEGE',
245,'CREATE EVALUATION CONTEXT',
246,'CREATE ANY EVALUATION CONTEXT',
247,'ALTER ANY EVALUATION CONTEXT',
248,'DROP ANY EVALUATION CONTEXT',
249,'EXECUTE ANY EVALUATION CONTEXT',
250,'CREATE RULE SET',
251,'CREATE ANY RULE SET',
252,'ALTER ANY RULE SET',
253,'DROP ANY RULE SET',
254,'EXECUTE ANY RULE SET',
255,'EXPORT FULL DATABASE',
256,'IMPORT FULL DATABASE',
257,'CREATE RULE',
258,'CREATE ANY RULE',
259,'ALTER ANY RULE',
260,'DROP ANY RULE',
261,'EXECUTE ANY RULE',
262,'ANALYZE ANY DICTIONARY',
263,'ADVISOR',
264,'CREATE JOB',
265,'CREATE ANY JOB',
266,'EXECUTE ANY PROGRAM',
267,'EXECUTE ANY CLASS',
268,'MANAGE SCHEDULER',
269,'SELECT ANY TRANSACTION',
270,'DROP ANY SQL PROFILE',
271,'ALTER ANY SQL PROFILE',
272,'ADMINISTER SQL TUNING SET',
273,'ADMINISTER ANY SQL TUNING SET',
274,'CREATE ANY SQL PROFILE',
275,'EXEMPT IDENTITY POLICY',
276,'MANAGE FILE GROUP',
277,'MANAGE ANY FILE GROUP',
278,'READ ANY FILE GROUP',
279,'CHANGE NOTIFICATION',
280,'CREATE EXTERNAL JOB',
281,'CREATE ANY EDITION',
282,'DROP ANY EDITION',
283,'ALTER ANY EDITION',
284,'CREATE ASSEMBLY',
285,'CREATE ANY ASSEMBLY',
286,'ALTER ANY ASSEMBLY',
287,'DROP ANY ASSEMBLY',
288,'EXECUTE ANY ASSEMBLY',
289,'EXECUTE ASSEMBLY',
290,'CREATE MINING MODEL',
291,'CREATE ANY MINING MODEL',
292,'DROP ANY MINING MODEL',
293,'SELECT ANY MINING MODEL',
294,'ALTER ANY MINING MODEL',
295,'COMMENT ANY MINING MODEL',
296,'ALTER MINING MODEL',
297,'COMMENT MINING MODEL',
298,'GRANT MINING MODEL',
299,'SELECT MINING MODEL',
301,'CREATE CUBE DIMENSION',
302,'ALTER ANY CUBE DIMENSION',
303,'CREATE ANY CUBE DIMENSION',
304,'DELETE ANY CUBE DIMENSION',
305,'DROP ANY CUBE DIMENSION',
306,'INSERT ANY CUBE DIMENSION',
307,'SELECT ANY CUBE DIMENSION',
308,'CREATE CUBE',
309,'ALTER ANY CUBE',
310,'CREATE ANY CUBE',
311,'DROP ANY CUBE',
312,'SELECT ANY CUBE',
313,'UPDATE ANY CUBE',
314,'CREATE MEASURE FOLDER',
315,'CREATE ANY MEASURE FOLDER',
316,'DELETE ANY MEASURE FOLDER',
317,'DROP ANY MEASURE FOLDER',
318,'INSERT ANY MEASURE FOLDER',
319,'CREATE CUBE BUILD PROCESS',
320,'CREATE ANY CUBE BUILD PROCESS',
321,'DROP ANY CUBE BUILD PROCESS',
322,'UPDATE ANY CUBE BUILD PROCESS',
323,'COMMENT EDITION',
324,'GRANT EDITION',
325,'USE EDITION',
326,'UPDATE ANY CUBE DIMENSION',
327,'ADMINISTER SQL MANAGEMENT OBJECT',
328,'ALTER PUBLIC DATABASE LINK',
329,'ALTER DATABASE LINK',
330,'DIRECT_PATH LOAD',
331,'DIRECT_PATH UNLOAD',
350,'FLASHBACK ARCHIVE ADMINISTER',
351,'EXEMPT REDACTION POLICY',
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,
s.sql_id,s.PREV_SQL_ID,FINAL_BLOCKING_SESSION_STATUS
from GV$LOCK L, GV$SESSION S,SYS.USER$ U1, SYS.OBJ$ T1
where L.SID = S.SID
and l.inst_id=s.inst_id
and T1.OBJ# = decode(L.ID2,0,L.ID1,1)
and U1.USER# = T1.OWNER#
and S.TYPE != 'BACKGROUND'
-- and S.TERMINAL is not null
and command!=0
and S.USERNAME not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
order by 1,2,5;
Kill String Username Term Table Name COMMAND Lock Held Lock Requested ID1 - ID2 Lock Type SQL_ID PREV_SQL_ID
--------------- ---------- ------------ ------------------------------ ------------------------- --------------- --------------- ------------------ ------------------------------ ------------- -------------
'179,3983,@1' ANSPRINT XXXXXXXXXXX SYS.ORA$BASE ALTER SYSTEM Share NONE 100-0 AE - ???? 1mtxshuygvxuj 459f3z9u4fb3u
'466,4335,@1' ANSPRINT XXXXXXXXXXX SYS.ORA$BASE ALTER SYSTEM Share NONE 100-0 AE - ???? 1mtxshuygvxuj 459f3z9u4fb3u
'495,41851,@1' ANSPRINT XXXXXXXXXXX SYS.ORA$BASE ALTER SYSTEM Share NONE 100-0 AE - ???? 1mtxshuygvxuj 459f3z9u4fb3u
'61,14015,@1' ANSPPP UNKNOWN None ALTER SYSTEM Exclusive NONE 1114137-102911270 TX - Transaction enqueue lock cv664khgc0nhy 0pwgs492bf10h
SYS.ORA$BASE ALTER SYSTEM Share NONE 100-0 AE - ???? cv664khgc0nhy 0pwgs492bf10h
ANSPPP.LOT_BATCH_MASTER_CFT SELECT ANY TABLE Row Exclusive NONE 123175-0 TM - DML enqueue lock 6h1r9ap9zhnjv 5tnn2kwbquuj4
6 rows selected.
===
set pagesize 200 linesize 500
column osuser heading 'OS|Username' format a7 truncate
column process heading 'OS|Process' format a7 truncate
column machine heading 'OS|Machine' format a10 truncate
column program heading 'OS|Program' format a25 truncate
column object heading 'Database|Object' format a25 truncate
column lock_type heading 'Lock|Type' format a4 truncate
column mode_held heading 'Mode|Held' format a15 truncate
column mode_requested heading 'Mode|Requested' format a10 truncate
column sid heading 'SID' format 999
column username heading 'Oracle|Username' format a7 truncate
column image heading 'Active Image' format a20 truncate
column sid format 99999
col waiting_session head 'WAITER' format 9999
col holding_session head 'BLOCKER' format 9999
col kill for a18
col event for a20
select /*+ ordered */
--b.kaddr,
--c.sid, c.inst_id,
''''||c.sid ||','|| c.serial#||',@'||c.inst_id ||'''' kill,
c.con_id,
c.sql_id,
c.event,
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
GV$lock b
,GV$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
/
