126 lines
4.6 KiB
SQL
126 lines
4.6 KiB
SQL
@@header
|
|
|
|
/*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Displays currently locked objects in database
|
|
* Parameters : 1 - owner (% - wildchar, \ - escape char) , this could also be passed as OWNER.OBJECT_NAME
|
|
* 2 - Object name (% - wildchar, \ - escape char)
|
|
* 3 - Object Type (% - wildchar, \ - escape char)
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 29-Jan-16 Vishal Gupta Switched from gv$locked_objects to gv$lock, as
|
|
* earlier view was giving information about locks on
|
|
* temporary tables (with preserve on commit rows setting).
|
|
* - Also added machine and process in the output
|
|
* 06-Sep-12 Vishal Gupta Added where clause to add more filtering
|
|
* 09-Aug-12 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE owner
|
|
UNDEFINE object_name
|
|
UNDEFINE object_type
|
|
UNDEFINE WHERE_CLAUSE
|
|
|
|
DEFINE owner="&&1"
|
|
DEFINE object_name="&&2"
|
|
DEFINE object_type="&&3"
|
|
DEFINE WHERE_CLAUSE="&&4"
|
|
|
|
|
|
COLUMN _owner NEW_VALUE owner NOPRINT
|
|
COLUMN _object_name NEW_VALUE object_name NOPRINT
|
|
COLUMN _object_type NEW_VALUE object_type NOPRINT
|
|
|
|
set term off
|
|
|
|
SELECT DECODE(UPPER('&&owner'),'','%','&&owner') "_owner"
|
|
, DECODE(UPPER('&&object_name'),'','%','&&object_name') "_object_name"
|
|
, DECODE(UPPER('&&object_type'),'','%','&&object_type') "_object_type"
|
|
FROM DUAL;
|
|
|
|
SELECT CASE
|
|
WHEN INSTR('&&owner','.') != 0 THEN SUBSTR(UPPER('&&owner'),1,INSTR('&&owner','.')-1)
|
|
ELSE DECODE(UPPER('&&owner'),'','%',UPPER('&&owner'))
|
|
END "_owner"
|
|
, CASE
|
|
WHEN INSTR('&&owner','.') != 0 THEN SUBSTR(UPPER('&&owner'),INSTR('&&owner','.')+1)
|
|
ELSE DECODE(UPPER('&&object_name'),'','%',UPPER('&&object_name'))
|
|
END "_object_name"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
|
|
|
|
PROMPT **************************************************************
|
|
PROMPT * L O C K E D O B J E C T S
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - Owner = '&&owner'
|
|
PROMPT * - Object Name = '&&object_name'
|
|
PROMPT * - Object Type = '&&object_type'
|
|
PROMPT * - Where Clause = "&&WHERE_CLAUSE"
|
|
PROMPT **************************************************************
|
|
|
|
COLUMN object_name HEADING "ObjectName" FORMAT a40
|
|
COLUMN object_type HEADING "ObjectType" FORMAT a10 TRUNCATED
|
|
COLUMN locked_mode HEADING "LockedMode" FORMAT a10
|
|
COLUMN sid HEADING "SID" FORMAT 99999
|
|
COLUMN inst_id HEADING "I#" FORMAT 99
|
|
COLUMN spid HEADING "SPID" FORMAT a6
|
|
COLUMN status HEADING "Status" FORMAT a8
|
|
COLUMN state HEADING "State" FORMAT a12 TRUNCATED
|
|
COLUMN logon_time HEADING "Logon Time" FORMAT a18
|
|
COLUMN username HEADING "UserName" FORMAT a20
|
|
COLUMN osuser HEADING "OS User" FORMAT a15 TRUNCATED
|
|
COLUMN MACHINE HEADING "Machine" FORMAT a20 TRUNCATED
|
|
COLUMN process HEADING "Process" FORMAT a11
|
|
COLUMN program HEADING "Program" FORMAT a18 TRUNCATED
|
|
COLUMN event HEADING "Event" FORMAT a30 TRUNCATED
|
|
COLUMN last_call_et HEADING "LastCall|(sec)" FORMAT a12
|
|
COLUMN sql_child_number HEADING "SQL|Child|No" FORMAT 99
|
|
|
|
SELECT o.owner || '.' || o.object_name object_name
|
|
, o.object_type
|
|
, DECODE(l.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 (SSX)', /* C */
|
|
6, 'Exclusive', /* X */
|
|
TO_CHAR(l.lmode)) locked_mode
|
|
, s.sid sid
|
|
, s.inst_id
|
|
, s.status
|
|
, s.username
|
|
, s.machine
|
|
, s.osuser
|
|
, s.program
|
|
, s.process
|
|
, s.event
|
|
FROM gv$lock l
|
|
JOIN dba_objects o ON o.object_id = l.id1
|
|
JOIN gv$session s ON s.inst_id = l.inst_id AND s.sid = l.sid
|
|
WHERE o.owner like '&&owner' ESCAPE '\'
|
|
AND o.object_name like '&&object_name' ESCAPE '\'
|
|
AND o.object_type like '&&object_type' ESCAPE '\'
|
|
&&WHERE_CLAUSE
|
|
ORDER BY object_name
|
|
;
|
|
|
|
|
|
UNDEFINE owner
|
|
UNDEFINE object_name
|
|
UNDEFINE object_type
|
|
UNDEFINE WHERE_CLAUSE
|
|
|
|
@@footer |