【轉】檢視Oracle資料庫阻塞
select /*+ ordered */
--b.kaddr,
--b.ID2,b.ID1,
c.sid,
lock_waiter.waiting_session,
lock_blocker.holding_session,
c.program,
c.osuser,
c.machine,
c.process,
decode(u.name,
null,'',
u.name||'.'||o.name
) object,
c.username,
decode
(
b.type,
'BL', 'Buffer hash table instance lock',
'CF', 'Control file schema global enqueue lock',
'CI', 'Cross-instance function invocation instance lock',
'CU', 'Cursor bind lock',
'DF', 'Data file instance lock',
'DL', 'direct loader parallel index create lock',
'DM', 'Mount/startup db primary/secondary instance lock',
'DR', 'Distributed recovery process lock',
'DX', 'Distributed transaction entry lock',
'FS', 'File set 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',
'LA','Library cache lock instance lock (A..P=namespace);',
'LB','Library cache lock instance lock (A..P=namespace);',
'LC','Library cache lock instance lock (A..P=namespace);',
'LD','Library cache lock instance lock (A..P=namespace);',
'LE','Library cache lock instance lock (A..P=namespace);',
'LF','Library cache lock instance lock (A..P=namespace);',
'LG','Library cache lock instance lock (A..P=namespace);',
'LH','Library cache lock instance lock (A..P=namespace);',
'LI','Library cache lock instance lock (A..P=namespace);',
'LJ','Library cache lock instance lock (A..P=namespace);',
'LK','Library cache lock instance lock (A..P=namespace);',
'LL','Library cache lock instance lock (A..P=namespace);',
'LM','Library cache lock instance lock (A..P=namespace);',
'LN','Library cache lock instance lock (A..P=namespace);',
'LO','Library cache lock instance lock (A..P=namespace);',
'LP','Library cache lock instance lock (A..P=namespace);',
'MM', 'Mount definition global enqueue lock',
'MR', 'Media recovery lock',
'NA', 'Library cache pin instance lock (A..Z=namespace)',
'NB', 'Library cache pin instance lock (A..Z=namespace)',
'NC', 'Library cache pin instance lock (A..Z=namespace)',
'ND', 'Library cache pin instance lock (A..Z=namespace)',
'NE', 'Library cache pin instance lock (A..Z=namespace)',
'NF', 'Library cache pin instance lock (A..Z=namespace)',
'NG', 'Library cache pin instance lock (A..Z=namespace)',
'NH', 'Library cache pin instance lock (A..Z=namespace)',
'NI', 'Library cache pin instance lock (A..Z=namespace)',
'NJ', 'Library cache pin instance lock (A..Z=namespace)',
'NK', 'Library cache pin instance lock (A..Z=namespace)',
'NL', 'Library cache pin instance lock (A..Z=namespace)',
'NM', 'Library cache pin instance lock (A..Z=namespace)',
'NN', 'Library cache pin instance lock (A..Z=namespace)',
'NO', 'Library cache pin instance lock (A..Z=namespace)',
'NP', 'Library cache pin instance lock (A..Z=namespace)',
'NQ', 'Library cache pin instance lock (A..Z=namespace)',
'NR', 'Library cache pin instance lock (A..Z=namespace)',
'NS', 'Library cache pin instance lock (A..Z=namespace)',
'NT', 'Library cache pin instance lock (A..Z=namespace)',
'NU', 'Library cache pin instance lock (A..Z=namespace)',
'NV', 'Library cache pin instance lock (A..Z=namespace)',
'NW', 'Library cache pin instance lock (A..Z=namespace)',
'NX', 'Library cache pin instance lock (A..Z=namespace)',
'NY', 'Library cache pin instance lock (A..Z=namespace)',
'NZ', 'Library cache pin instance lock (A..Z=namespace)',
'PF', 'Password File lock',
'PI', 'Parallel operation locks',
'PS', 'Parallel operation locks',
'PR', 'Process startup lock',
'QA','Row cache instance lock (A..Z=cache)',
'QB','Row cache instance lock (A..Z=cache)',
'QC','Row cache instance lock (A..Z=cache)',
'QD','Row cache instance lock (A..Z=cache)',
'QE','Row cache instance lock (A..Z=cache)',
'QF','Row cache instance lock (A..Z=cache)',
'QG','Row cache instance lock (A..Z=cache)',
'QH','Row cache instance lock (A..Z=cache)',
'QI','Row cache instance lock (A..Z=cache)',
'QJ','Row cache instance lock (A..Z=cache)',
'QK','Row cache instance lock (A..Z=cache)',
'QL','Row cache instance lock (A..Z=cache)',
'QM','Row cache instance lock (A..Z=cache)',
'QN','Row cache instance lock (A..Z=cache)',
'QP','Row cache instance lock (A..Z=cache)',
'QQ','Row cache instance lock (A..Z=cache)',
'QR','Row cache instance lock (A..Z=cache)',
'QS','Row cache instance lock (A..Z=cache)',
'QT','Row cache instance lock (A..Z=cache)',
'QU','Row cache instance lock (A..Z=cache)',
'QV','Row cache instance lock (A..Z=cache)',
'QW','Row cache instance lock (A..Z=cache)',
'QX','Row cache instance lock (A..Z=cache)',
'QY','Row cache instance lock (A..Z=cache)',
'QZ','Row cache instance lock (A..Z=cache)',
'RT', 'Redo thread global enqueue lock',
'SC', 'System commit number instance lock',
'SM', 'SMON lock',
'SN', 'Sequence number instance lock',
'SQ', 'Sequence number enqueue lock',
'SS', 'Sort segment locks',
'ST', 'Space transaction enqueue lock',
'SV', 'Sequence number value lock',
'TA', 'Generic enqueue lock',
'TS', 'Temporary segment enqueue lock (ID2=0)',
'TS', 'New block allocation enqueue lock (ID2=1)',
'TT', 'Temporary table enqueue lock',
'UN', 'User name lock',
'US', 'Undo segment DDL lock',
'WL', 'Being-written redo log instance lock',
b.type
) lock_type,
decode
(
b.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 (SRX)', /* C */
6, 'Exclusive', /* X */
to_char(b.lmode)
) mode_held,
decode
(
b.request,
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(b.request)
) mode_requested
from
v$lock b
,v$session c
,sys.user$ u
,sys.obj$ o
,( select * from sys.dba_waiters) lock_blocker
,( select * from sys.dba_waiters) lock_waiter
where
b.sid = c.sid
and u.user# = c.user#
and o.obj#(+) = b.id1
and lock_blocker.waiting_session(+) = c.sid
and lock_waiter.holding_session(+) = c.sid
and c.username = 'DBMGR'
order by b.ID2,b.ID1,kaddr, lockwait
/
Link URL: http://hi.baidu.com/ljm0211/blog/item/802d3b877b9a2e0ac75cc3ad.html
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/11411056/viewspace-732751/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 檢視oracle資料庫----sizeOracle資料庫
- oracle資料庫巡檢(轉)Oracle資料庫
- 資料庫檢視詳解 (轉)資料庫
- 【轉載】Oracle資料字典檢視Oracle
- Oracle資料庫DBA日常Sql列表及常用檢視(轉)Oracle資料庫SQL
- 檢視oracle資料庫真實大小Oracle資料庫
- Oracle 資料庫字典 檢視 基表Oracle資料庫
- 檢視oracle資料庫字符集Oracle資料庫
- 檢視各項Oracle資料庫資訊Oracle資料庫
- ORACLE之檢視資料庫的SQLOracle資料庫SQL
- 資料庫檢視資料庫
- 資料庫-檢視資料庫
- ORACLE資料庫檢視執行計劃Oracle資料庫
- 檢視oracle資料庫是否歸檔和修改歸檔模式(轉)Oracle資料庫模式
- 檢視oracle資料庫的連線數以及使用者檢視Oracle資料庫
- ORACLE資料庫檢視ACQ(ACTIVE CHECKPOINT QUEUE)資訊Oracle資料庫
- Oracle資料庫中物化檢視的原理剖析Oracle資料庫
- 檢視oracle資料庫是否為歸檔模式Oracle資料庫模式
- Oracle資料庫檢視使用者狀態Oracle資料庫
- (轉)oracle資料庫之間 表複製方法二(物化檢視 方法)Oracle資料庫
- Windows使用Apche並檢視MySQL資料庫(轉)WindowsMySql資料庫
- MySQL資料庫檢視:檢視定義、建立檢視、修改檢視MySql資料庫
- 資料庫無法建立資料庫檢視資料庫
- oracle資料庫巡檢優化-快速定位資料庫瓶頸(轉)Oracle資料庫優化
- 【檢視】oracle 資料字典檢視之 DICT / DICTIONARYOracle
- Oracle 資料庫檢視與基表的關係Oracle資料庫
- Oracle常用命令 檢視資料庫的SQLOracle資料庫SQL
- Oracle資料庫巡檢Oracle資料庫
- (轉)oracle 資料庫效能健康檢查指令碼Oracle資料庫指令碼
- (轉)檢視sql server 資料庫連線數方法SQLServer資料庫
- 資料庫檢視詳解資料庫
- 檢視資料庫的版本資料庫
- 資料庫的物化檢視資料庫
- 資料庫檢視的作用資料庫
- 資料庫檢視的使用資料庫
- oracle常用資料字典.檢視Oracle
- MySQL檢視當前資料庫庫MySql資料庫
- 持續更新,檢視dump oracle資料塊檢視Oracle