set termout off store set sqlplus_settings replace set serveroutput on size 1000000 set linesize 180 set pagesize 120 set termout off set verify off set head off ttitle off btitle off clear columns clear breaks PROMPT PROMPT ###################################################################### PROMPT PROMPT -- Name : listLockingInfo.sql PROMPT PROMPT -- Copyright (c) DTA Inc., All Rights Reserved (2003) PROMPT PROMPT -- Description :This script lists the locking information on all locks PROMPT for the database to which you are connected OR locks for PROMPT the specified user only PROMPT PROMPT -- Input(s) : None PROMPT PROMPT -- Output : The output is in a file named lockingInfo__.lst PROMPT PROMPT -- Note : Run this script while connected as the SYS user PROMPT ###################################################################### PROMPT column sid format 999 heading "SID" column serial# format 99999 heading "Serial|Number" column username format a10 heading "User Name" wrap column terminal format a15 heading "Terminal" wrap column res format a20 heading "Resource Type" word_wrap column owner format a8 heading "Owner" wrap column tab format a15 heading "Object|Name" wrap column id1 format 999999999 heading "Lock ID1" column id2 format 999999999 heading "Lock ID2" column lmode format a14 heading "Lock Held" column request format a14 heading "Lock Request" column command format a24 heading 'Command" word_wrap -- Get database name and store in variable column name new_value s_dbname SELECT rtrim(name) name from v$database; -- Get today's date column today new_value s_curDate SELECT to_char(sysdate, 'Month DD, YYYY') today from dual; -- Get host name and store in variable column host_name new_value s_machine SELECT host_name from v$instance; set termout on -- allow user name to be entered. NULL/return key is for all locks/users accept u_name prompt 'Enter the name of user to check locks for (return for all users) : ' set termout off set feedback off set head on spool lockingInfo_&s_dbname._&s_machine ttitle left &s_curDate - center "Database Locking Information for " &s_dbname " on " &s_machine skip 2 -- Get Locking information select l.sid, s.serial#, nvl(s.username,'Internal') username, nvl(s.terminal,'None') terminal, decode(l.type, 'BL','BL - Buffer hash table instance lock', 'CF','CF - Control file schema global enqueue lock', 'CI','CI - Cross-instance function invocation instance lock', 'CS','CS - Control file schema global enqueue lock', 'CU','CU - Cursor bind lock', 'DF','DF - Data file instance lock', 'DL','DL - Direct loader parallel index create', 'DM','DM - Mount/startup db primary/secondary instance lock', 'DR','DR - Distributed recovery process lock', 'DX','DX - Distributed transaction entry lock', 'FI','FI - SGA open-file information lock', 'FS','FS - File set lock', 'HW','HW - Space management operations on a specific segment lock', 'IN','IN - Instance number lock', 'IR','IR - Instance recovery serialization global enqueue lock', 'IS','IS - Instance state lock', 'IV','IV - Library cache invalidation instance lock', 'JQ','JQ - Job queue lock', 'KK','KK - Thread kick lock', 'MB','MB - Master buffer hash table instance lock', 'MM','MM - Mount definition gloabal enqueue lock', 'MR','MR - Media recovery lock', 'PF','PF - Password file lock', 'PI','PI - Parallel operation lock', 'PR','PR - Process startup lock', 'PS','PS - Parallel operation lock', 'RE','RE - USE_ROW_ENQUEUE enforcement lock', 'RT','RT - Redo thread global enqueue lock', 'RW','RW - Row wait enqueue lock', 'SC','SC - System commit number instance lock', 'SH','SH - System commit number high water mark enqueue lock', 'SM','SM - SMON lock', 'SN','SN - Sequence number instance lock', 'SQ','SQ - Sequence number enqueue lock', 'SS','SS - Sort segment lock', 'ST','ST - Space transaction enqueue lock', 'SV','SV - Sequence number value lock', 'TA','TA - Generic enqueue lock', 'TD','TD - DDL enqueue lock', 'TE','TE - Extend-segment enqueue lock', 'TM','TM - DML enqueue lock', 'TT','TT - Temporary table enqueue lock', 'TX','TX - Transaction enqueue lock', 'UL','UL - User supplied lock', 'UN','UN - User name lock', 'US','US - Undo segment DDL lock', 'WL','WL - Being-written redo log instance lock', 'WS','WS - Write-atomic-log-switch global enqueue lock', 'TS',decode(l.id2,0,'TS - Temporary segment enqueue lock (ID2=0)', 'TS - New block allocation enqueue lock (ID2=1)'), 'LA','LA - Library cache lock instance lock (A=namespace)', 'LB','LB - Library cache lock instance lock (B=namespace)', 'LC','LC - Library cache lock instance lock (C=namespace)', 'LD','LD - Library cache lock instance lock (D=namespace)', 'LE','LE - Library cache lock instance lock (E=namespace)', 'LF','LF - Library cache lock instance lock (F=namespace)', 'LG','LG - Library cache lock instance lock (G=namespace)', 'LH','LH - Library cache lock instance lock (H=namespace)', 'LI','LI - Library cache lock instance lock (I=namespace)', 'LJ','LJ - Library cache lock instance lock (J=namespace)', 'LK','LK - Library cache lock instance lock (K=namespace)', 'LL','LL - Library cache lock instance lock (L=namespace)', 'LM','LM - Library cache lock instance lock (M=namespace)', 'LN','LN - Library cache lock instance lock (N=namespace)', 'LO','LO - Library cache lock instance lock (O=namespace)', 'LP','LP - Library cache lock instance lock (P=namespace)', 'LS','LS - Log start/log switch enqueue lock', 'PA','PA - Library cache pin instance lock (A=namespace)', 'PB','PB - Library cache pin instance lock (B=namespace)', 'PC','PC - Library cache pin instance lock (C=namespace)', 'PD','PD - Library cache pin instance lock (D=namespace)', 'PE','PE - Library cache pin instance lock (E=namespace)', 'PF','PF - Library cache pin instance lock (F=namespace)', 'PG','PG - Library cache pin instance lock (G=namespace)', 'PH','PH - Library cache pin instance lock (H=namespace)', 'PI','PI - Library cache pin instance lock (I=namespace)', 'PJ','PJ - Library cache pin instance lock (J=namespace)', 'PL','PL - Library cache pin instance lock (K=namespace)', 'PK','PK - Library cache pin instance lock (L=namespace)', 'PM','PM - Library cache pin instance lock (M=namespace)', 'PN','PN - Library cache pin instance lock (N=namespace)', 'PO','PO - Library cache pin instance lock (O=namespace)', 'PP','PP - Library cache pin instance lock (P=namespace)', 'PQ','PQ - Library cache pin instance lock (Q=namespace)', 'PR','PR - Library cache pin instance lock (R=namespace)', 'PS','PS - Library cache pin instance lock (S=namespace)', 'PT','PT - Library cache pin instance lock (T=namespace)', 'PU','PU - Library cache pin instance lock (U=namespace)', 'PV','PV - Library cache pin instance lock (V=namespace)', 'PW','PW - Library cache pin instance lock (W=namespace)', 'PX','PX - Library cache pin instance lock (X=namespace)', 'PY','PY - Library cache pin instance lock (Y=namespace)', 'PZ','PZ - Library cache pin instance lock (Z=namespace)', 'QA','QA - Row cache instance lock (A=cache)', 'QB','QB - Row cache instance lock (B=cache)', 'QC','QC - Row cache instance lock (C=cache)', 'QD','QD - Row cache instance lock (D=cache)', 'QE','QE - Row cache instance lock (E=cache)', 'QF','QF - Row cache instance lock (F=cache)', 'QG','QG - Row cache instance lock (G=cache)', 'QH','QH - Row cache instance lock (H=cache)', 'QI','QI - Row cache instance lock (I=cache)', 'QJ','QJ - Row cache instance lock (J=cache)', 'QL','QL - Row cache instance lock (K=cache)', 'QK','QK - Row cache instance lock (L=cache)', 'QM','QM - Row cache instance lock (M=cache)', 'QN','QN - Row cache instance lock (N=cache)', 'QO','QO - Row cache instance lock (O=cache)', 'QP','QP - Row cache instance lock (P=cache)', 'QQ','QQ - Row cache instance lock (Q=cache)', 'QR','QR - Row cache instance lock (R=cache)', 'QS','QS - Row cache instance lock (S=cache)', 'QT','QT - Row cache instance lock (T=cache)', 'QU','QU - Row cache instance lock (U=cache)', 'QV','QV - Row cache instance lock (V=cache)', 'QW','QW - Row cache instance lock (W=cache)', 'QX','QX - Row cache instance lock (X=cache)', 'QY','QY - Row cache instance lock (Y=cache)', 'QZ','QZ - Row cache instance lock (Z=cache)', l.type ||' - System') res, u.name owner, t.name tab, l.id1, l.id2, decode(l.lmode, 0,'None', 1,'No Lock', 2,'Row Share', 3,'Row Exclusive', 4,'Share', 5,'Shr Row Excl', 6,'Exclusive',null) lmode, decode(l.request, 0,'None', 1,'No Lock', 2,'Row Share', 3,'Row Excl', 4,'Share', 5,'Shr Row Excl', 6,'Exclusive',null) request, decode(s.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', s.command ||' - UNKNOWN') || decode(tx.sql_text,NULL,NULL,' - ' || tx.sql_text) command from v$lock l, v$session s, v$sqlarea tx, sys.user$ u, sys.obj$ t where l.sid = s.sid -- and s.type != 'BACKGROUND' and t.obj# = l.id1 and u.user# = t.owner# and s.sql_hash_value = tx.hash_value (+) and nvl(s.username,1) like upper(nvl('&&u_name','%')) order by id1, request; spool off ttitle off btitle off clear columns clear breaks set feedback on set termout on set verify on PROMPT PROMPT -- If file is empty, no lock information was found for the database &s_dbname PROMPT -- or no lock information was found for the username &u_name PROMPT PROMPT PROMPT Output file lockingInfo_&s_dbname._&s_machine..LST produced PROMPT undefine u_name @sqlplus_settings