【Script】查詢鎖狀態的同時展示出鎖型別資訊
如果對V$LOCK檢視比較熟悉的話，一定知道其中的TYPE欄位記錄了鎖的型別，本文將使用這個檢視完成系統鎖資訊查詢。
本文中指令碼所使用到的檢視如下：V$LOCK，V$SESSION，SYS.USER$和SYS.OBJ$。
1.開啟一個session，完成T表的刪除
sec@ora10g> delete from t;
12 rows deleted.
2.另外開啟一個session，再次執行同樣的刪除操作
sec@ora10g> delete from t;
3.使用指令碼檢視當前系統中存在的鎖情況
User Name SID TERMINAL Object Name COMMAND LMODE REQUEST LADDR LOCKTYPTE
-------------------- ---------- ---------- --------------- ---------- --------------- ------------------- -------------------- ------------------------------
SEC 529 pts/4 SEC.T DELETE Row Exclusive NONE 87559-0 TM - DML enqueue lock
SEC 529 pts/4 None DELETE NONE Exclusive 655375-125464 TX - Transaction enqueue lock
SEC 530 pts/3 None SELECT Exclusive NONE 655375-125464 TX - Transaction enqueue lock
SEC 530 pts/3 SEC.T SELECT Row Exclusive NONE 87559-0 TM - DML enqueue lock
4.指令碼內容如下
col "User Name" for a20
col terminal for a10
col "Object Name" for a15
col command for a10
col laddr for a20
col locktypte for a30
col lmode for a15
SELECT NVL (VSESSION.USERNAME, 'Internal') "User Name",
VLOCK.SID,
NVL (VSESSION.TERMINAL, 'None') terminal,
DECODE (
command,
0,
'None',
DECODE (VLOCK.id2,
0, SYSUSER.NAME || '.' || SUBSTR (SYSOBJ.NAME, 1, 20),
'None')
)
"Object Name",
DECODE (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',
command || ' - ???')
COMMAND,
DECODE (VLOCK.LMODE,
1, 'No Lock',
2, 'Row Share',
3, 'Row Exclusive',
4, 'Share',
5, 'Share Row Exclusive',
6, 'Exclusive',
'NONE')
lmode,
DECODE (VLOCK.REQUEST,
1, 'No Lock',
2, 'Row Share',
3, 'Row Exclusive',
4, 'Share',
5, 'Share Row Exclusive',
6, 'Exclusive',
'NONE')
request,
VLOCK.id1 || '-' || VLOCK.id2 Laddr,
VLOCK.TYPE || ' - '
|| DECODE (
VLOCK.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',
'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 (VLOCK.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)',
'????'
)
locktypte
FROM V$LOCK VLOCK,
V$SESSION VSESSION,
SYS.USER$ SYSUSER,
SYS.OBJ$ SYSOBJ
WHERE VLOCK.SID = VSESSION.SID
AND SYSOBJ.OBJ# = DECODE (VLOCK.ID2, 0, VLOCK.ID1, 1)
AND SYSUSER.USER# = SYSOBJ.OWNER#
AND VSESSION.TYPE != 'BACKGROUND'
ORDER BY 1, 2, 5
/
5.V$LOCK檢視參考資訊
6.小結
透過本文中的指令碼可以很迅速的掌握系統中我們需要關注的鎖資訊內容，對於故障排查不無裨益。
Good luck.
secooler
10.05.03
-- The End --
本文中指令碼所使用到的檢視如下：V$LOCK，V$SESSION，SYS.USER$和SYS.OBJ$。
1.開啟一個session，完成T表的刪除
sec@ora10g> delete from t;
12 rows deleted.
2.另外開啟一個session，再次執行同樣的刪除操作
sec@ora10g> delete from t;
3.使用指令碼檢視當前系統中存在的鎖情況
User Name SID TERMINAL Object Name COMMAND LMODE REQUEST LADDR LOCKTYPTE
-------------------- ---------- ---------- --------------- ---------- --------------- ------------------- -------------------- ------------------------------
SEC 529 pts/4 SEC.T DELETE Row Exclusive NONE 87559-0 TM - DML enqueue lock
SEC 529 pts/4 None DELETE NONE Exclusive 655375-125464 TX - Transaction enqueue lock
SEC 530 pts/3 None SELECT Exclusive NONE 655375-125464 TX - Transaction enqueue lock
SEC 530 pts/3 SEC.T SELECT Row Exclusive NONE 87559-0 TM - DML enqueue lock
4.指令碼內容如下
col "User Name" for a20
col terminal for a10
col "Object Name" for a15
col command for a10
col laddr for a20
col locktypte for a30
col lmode for a15
SELECT NVL (VSESSION.USERNAME, 'Internal') "User Name",
VLOCK.SID,
NVL (VSESSION.TERMINAL, 'None') terminal,
DECODE (
command,
0,
'None',
DECODE (VLOCK.id2,
0, SYSUSER.NAME || '.' || SUBSTR (SYSOBJ.NAME, 1, 20),
'None')
)
"Object Name",
DECODE (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',
command || ' - ???')
COMMAND,
DECODE (VLOCK.LMODE,
1, 'No Lock',
2, 'Row Share',
3, 'Row Exclusive',
4, 'Share',
5, 'Share Row Exclusive',
6, 'Exclusive',
'NONE')
lmode,
DECODE (VLOCK.REQUEST,
1, 'No Lock',
2, 'Row Share',
3, 'Row Exclusive',
4, 'Share',
5, 'Share Row Exclusive',
6, 'Exclusive',
'NONE')
request,
VLOCK.id1 || '-' || VLOCK.id2 Laddr,
VLOCK.TYPE || ' - '
|| DECODE (
VLOCK.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',
'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 (VLOCK.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)',
'????'
)
locktypte
FROM V$LOCK VLOCK,
V$SESSION VSESSION,
SYS.USER$ SYSUSER,
SYS.OBJ$ SYSOBJ
WHERE VLOCK.SID = VSESSION.SID
AND SYSOBJ.OBJ# = DECODE (VLOCK.ID2, 0, VLOCK.ID1, 1)
AND SYSUSER.USER# = SYSOBJ.OWNER#
AND VSESSION.TYPE != 'BACKGROUND'
ORDER BY 1, 2, 5
/
5.V$LOCK檢視參考資訊
6.小結
透過本文中的指令碼可以很迅速的掌握系統中我們需要關注的鎖資訊內容，對於故障排查不無裨益。
Good luck.
secooler
10.05.03
-- The End --
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/519536/viewspace-661852/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL探祕(五):InnoDB鎖的型別和狀態查詢MySql型別
- mysql 鎖狀態的一些狀態資訊記錄MySql
- oracle 查詢鎖 && 解鎖Oracle
- Oracle查詢鎖、解鎖Oracle
- SQL SERVER死鎖查詢，死鎖分析，解鎖，查詢佔用SQLServer
- mysql鎖表查詢MySql
- 金蝶雲星空解鎖時同時解鎖序列號
- openguass 資料庫狀態查詢資料庫
- 執行緒狀態和鎖執行緒
- 偏向鎖狀態轉移原理
- InnoDB儲存引擎鎖機制（二、 鎖的型別）儲存引擎型別
- synchronized四種鎖狀態的升級synchronized
- java執行緒的狀態+鎖分析Java執行緒
- 死鎖避免中的安全狀態和不安全狀態
- 【YashanDB資料庫】yasboot查詢資料庫狀態時顯示資料庫狀態為off資料庫boot
- MySQL 中的鎖有哪些型別，MySQL 中加鎖的原則MySql型別
- sql 開發篇一 之 表鎖查詢及解鎖SQL
- mysql metadata lock後設資料鎖之鎖狀態lock_status流轉圖MySql
- Java執行緒狀態及同步鎖Java執行緒
- SQL Server 資料庫查詢死鎖的處理步驟SQLServer資料庫
- MySQL鎖表相關問題查詢思路MySql
- mysql慢查詢，死鎖解決方案MySql
- 關於 鎖的四種狀態與鎖升級過程 圖文詳解
- oracle資料庫事務transaction 不同的鎖lock型別Oracle資料庫型別
- 會話和鎖資訊查詢檢視 | 全方位認識 sys 系統庫會話
- 原子鎖和普通鎖的區別
- Java的型別化狀態機Java型別
- Laravel6 使用騰訊雲簡訊-簡訊模板狀態查詢Laravel
- python資料庫查詢返回時間為datetime型別時候的特殊處理Python資料庫型別
- MongoDB日期型別查詢MongoDB型別
- 查詢同時附帶查主表的第二列
- mysql資料庫時間型別datetime、bigint、timestamp的查詢效率比較MySql資料庫型別
- Elasticsearch 7.x Nested 巢狀型別查詢 | ES 乾貨Elasticsearch巢狀型別
- Laravel6 使用騰訊雲簡訊-簡訊簽名狀態查詢Laravel
- 使用jstack檢測Java應用的死鎖(deadlock)狀態JSJava
- React的靜態型別檢查React型別
- Mysql中S 鎖和 X 鎖的區別MySql
- 關於synchronized的理解，共有兩種型別的鎖：synchronized型別
- INSERT...SELECT語句對查詢的表加鎖嗎