-- FILE: ges_enqueue.sql -- -- DESCRIPTION: -- This script attempts to list all blocking or blocked -- global enqueues. -- -- AUTHOR: Andy Rivenes, www.appsdba.com -- Copyright (C) 2009-2010 AppsDBA Consulting -- -- DATE: 11/13/2009 -- -- REQUIREMENTS: -- -- MODIFICATIONS: -- 11/24/2009, AR, Added additional inst_id join between gv$session -- and gv$process to prevent duplicate rows. -- -- -- This program is free software: you can redistribute it and/or modify -- it under the terms of the GNU General Public License as published by -- the Free Software Foundation, either version 3 of the License, or -- (at your option) any later version. -- -- This program is distributed in the hope that it will be useful, -- but WITHOUT ANY WARRANTY; without even the implied warranty of -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the -- GNU General Public License for more details. -- -- You should have received a copy of the GNU General Public License -- along with this program. If not, see . -- -- SET PAGESIZE 9999; SET VERIFY off; SET FEEDBACK off; SET LINESIZE 200; -- COLUMN uname HEADING 'User/Program|Name' FORMAT A12; COLUMN inst_id HEADING 'Inst|ID' FORMAT 9999; COLUMN sid HEADING 'Oracle|SID' FORMAT 999999; COLUMN spid HEADING 'Server|PID' FORMAT A8; COLUMN command HEADING 'Command' FORMAT A15 WORD_WRAPPED; COLUMN lockwait HEADING 'Lockwait|Address' FORMAT A8; COLUMN sql_id HEADING 'SQL Id' FORMAT A13; COLUMN rw_obj HEADING 'Row Wait|Object#' FORMAT 999999; COLUMN BSS HEADING 'Blocking|Session|Status' FORMAT A11; COLUMN BI HEADING 'Blocking|Instance' FORMAT 9999; COLUMN BS HEADING 'Blocking|Session' FORMAT 999999; COLUMN event HEADING 'Event' FORMAT A15 WORD_WRAPPED; COLUMN wait_time HEADING 'Wait|Time' FORMAT 9999; COLUMN siw HEADING 'Seconds|In Wait' FORMAT 9999999; COLUMN state HEADING 'State' FORMAT A10 WORD_WRAPPED; -- SELECT NVL(a.username, b.program) uname, b.inst_id, a.sid, b.spid, DECODE( a.command, 0, 'No command', 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', 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 DATABASE LINK', 33, 'DROP DATABASE LINK', 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', 67, 'DROP PROFILE', 68, 'ALTER PROFILE', 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', 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', 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', 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', TO_CHAR(a.command) ) command, a.lockwait, a.sql_id, a.row_wait_obj# rw_obj, a.BLOCKING_SESSION_STATUS BSS, a.BLOCKING_INSTANCE BI, a.BLOCKING_SESSION BS, a.event, a.wait_time, a.seconds_in_wait siw, a.state FROM gv$session a, gv$process b, gv$ges_enqueue c WHERE a.paddr = b.addr AND a.inst_id = b.inst_id AND b.inst_id = c.inst_id AND b.spid = c.pid AND (c.blocked = 1 OR c.blocker = 1) AND a.command != 0 /