TA的每日心情 | 开心 2023-8-9 11:05 |
---|
|
楼主 |
发表于 2023-12-27 10:45:06
|
显示全部楼层
- REM srdc_lock_lolders.sql - collect Lock holder information
- define SRDCNAME='Lock_Holders'
- SET MARKUP HTML ON PREFORMAT ON
- set TERMOUT off FEEDBACK off VERIFY off TRIMSPOOL on HEADING off
- COLUMN SRDCSPOOLNAME NOPRINT NEW_VALUE SRDCSPOOLNAME
- select 'SRDC_'||upper('&&SRDCNAME')||'_'||upper(instance_name)||'_'||
- to_char(sysdate,'YYYYMMDD_HH24MISS') SRDCSPOOLNAME from v$instance;
- set TERMOUT on MARKUP html preformat on
- REM
- spool &&SRDCSPOOLNAME..htm
- select '+----------------------------------------------------+' from dual
- union all
- select '| Diagnostic-Name: '||'&&SRDCNAME' from dual
- union all
- select '| Timestamp: '||
- to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS TZH:TZM') from dual
- union all
- select '| Machine: '||host_name from v$instance
- union all
- select '| Version: '||version from v$instance
- union all
- select '| DBName: '||name from v$database
- union all
- select '| Instance: '||instance_name from v$instance
- union all
- select '+----------------------------------------------------+' from dual
- /
- set linesize 132 pagesize 66 heading on
- break on Kill 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 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 a7
- column tab format a35 heading "Table Name"
- column owner format a9
- column Address format a18
- column ctime heading "Held for (s)"
- 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,
- l.ctime
- 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
- /
-
- spool off
- set markup html off preformat off
复制代码 |
|