【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型別
- ｏｒａｃｌｅ 鎖資訊查詢
- 查詢鎖的狀況的物件物件
- SQL SERVER 查詢鎖資訊SQLServer
- 鎖表的相關資訊查詢
- sqlserver查詢時不加鎖SQLServer
- mysql 鎖狀態的一些狀態資訊記錄MySql
- package鎖的查詢與解鎖Package
- 查詢鎖的原因
- innodb查詢鎖
- TX鎖查詢
- [鎖機制] 鎖相關查詢
- 查詢鎖表 - 誰鎖住了我的表
- mysql鎖表查詢MySql
- 檢查Oracle的鎖狀態並清除問題會話Oracle會話
- Oracle中的鎖型別Oracle型別
- 查詢鎖表記錄
- MySql 鎖表 查詢 命令MySql
- 查詢鎖等待情況
- 查詢物件是否被鎖物件
- Oracle阻塞(鎖等待)查詢Oracle
- 執行緒狀態和鎖執行緒
- 偏向鎖狀態轉移原理
- InnoDB儲存引擎鎖機制（二、 鎖的型別）儲存引擎型別
- 查詢session被鎖的sql(轉)SessionSQL
- 查詢並解除oracle的死鎖Oracle
- synchronized四種鎖狀態的升級synchronized
- java執行緒的狀態+鎖分析Java執行緒
- 確定enqueue鎖型別ENQ型別
- MySQL 5.7 查詢InnoDB鎖表MySql
- oracle 查詢殺死死鎖方案Oracle
- oracle 死鎖查詢處理Oracle
- db2_查詢鎖方法DB2
- oracle 中查詢被鎖的物件，並殺死死鎖程式的方法Oracle物件
- MySQL 中的鎖有哪些型別，MySQL 中加鎖的原則MySql型別
- RMAN筆記之查詢rman備份資訊狀態筆記
- RAC環境中的阻塞 查詢鎖
- 從trc查詢死鎖的問題