通過shell指令碼檢視鎖資訊
在生產環境維護中,如果碰到較長時間的鎖等待,很可能會造成各種可能的問題。我們可以使用如下的指令碼來實時監控鎖的情況。
sqlplus -S $DB_CONN_STR@$SH_DB_SID <
set linesize 160
set pages 100
set feedback off
set verify off
set echo on
col object_name format a25
col osuser format a10
col machine format a12
col program format a20
--col object_type format a10
col state format a10
col status format a10
col oracle_username format a12
col sid_serial format a12
col sec_wait format 99999999
col lock_type format a5
col mode_held format a10
set linesize 200
prompt Current Locks
prompt --------------
select ses.sid||','||ses.serial# sid_serial,loc.oracle_username,object_name,
--object_type,
ses.LOGON_TIME,ses.SECONDS_IN_WAIT sec_wait,ses.osuser,ses.machine,ses.program,ses.state,ses.status,
decode(d.type,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distrib Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
d.type) lock_type,
decode(d.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(d.lmode)) mode_held
from v\$locked_object loc,v\$session ses,dba_objects obj,v\$lock d
where loc.object_id=obj.object_id
and loc.session_id=ses.sid
and obj.object_id=d.id1
and ses.sid=d.sid
order by oracle_username,seconds_in_wait desc
;
set head off
SELECT 'There are also '||count(*)||' transaction locks'
FROM v\$transaction_enqueue
;
prompt
prompt Blocking Session Details
select BLOCKING_SESSION ||' IS BLOCKING '||sid||','||serial# from v\$session where blocking_session is not null;
exit
EOF
指令碼執行的結果如下:
Current Locks
-------------
SID_SERIAL ORACLE_USERN OBJECT_NAME LOGON_TIM SEC_WAIT OSUSER MACHINE PROGRAM STATE STATUS LOCK_ MODE_HELD
------------ ------------ ------------------------- --------- --------- ---------- ------------ -------------------- ---------- ---------- ----- ----------
5480,2261 PRODUSER TMP_CONTROL 04-DEC-14 419010 mwrk01 fromClient1 JDBC Thin Client WAITING INACTIVE DML Row-X (SX)
5480,2261 PRODUSER TMP_CONTROL 04-DEC-14 419010 mwrk01 fromClient1 JDBC Thin Client WAITING INACTIVE DML Row-X (SX)
4086,35783 PRODUSER TMP_CONTROL 08-DEC-14 44446 mwrk01 fromClient1 JDBC Thin Client WAITING INACTIVE DML Row-X (SX)
4086,35783 PRODUSER TMP_CONTROL 08-DEC-14 44446 mwrk01 fromClient1 JDBC Thin Client WAITING INACTIVE DML Row-X (SX)
7190,24855 PRODUSER TMP_CONTROL 09-DEC-14 8601 mwrk01 fromClient1 JDBC Thin Client WAITING INACTIVE DML Row-X (SX)
7190,24855 PRODUSER TMP_CONTROL 09-DEC-14 8601 mwrk01 fromClient2 JDBC Thin Client WAITING INACTIVE DML Row-X (SX)
8322,17323 PRODUSER BILL_SUMMARY 09-DEC-14 1597 lwrk01 fromClient2 sqlplus@ccbdbpr1 (TNS V1-V3) WAITING ACTIVE DML Row-X (SX)
5179,65435 PRODUSER TMP_BULK_LOCKING 09-DEC-14 296 mwrk01 fromClient2 JDBC Thin Client WAITING INACTIVE DML Row-X (SX)
5179,65435 PRODUSER TMP_BULK_LOCKING 09-DEC-14 296 mwrk01 fromClient2 JDBC Thin Client WAITING INACTIVE TO Row-X (SX)
5179,65435 PRODUSER TEMP_DAEMON_LOCKING 09-DEC-14 296 mwrk01 fromClient2 JDBC Thin Client WAITING INACTIVE DML Row-X (SX)
5179,65435 PRODUSER TMP_BULK_LOCKING 09-DEC-14 296 mwrk01 fromClient2 JDBC Thin Client WAITING INACTIVE TO Row-X (SX)
sqlplus -S $DB_CONN_STR@$SH_DB_SID <
set pages 100
set feedback off
set verify off
set echo on
col object_name format a25
col osuser format a10
col machine format a12
col program format a20
--col object_type format a10
col state format a10
col status format a10
col oracle_username format a12
col sid_serial format a12
col sec_wait format 99999999
col lock_type format a5
col mode_held format a10
set linesize 200
prompt Current Locks
prompt --------------
select ses.sid||','||ses.serial# sid_serial,loc.oracle_username,object_name,
--object_type,
ses.LOGON_TIME,ses.SECONDS_IN_WAIT sec_wait,ses.osuser,ses.machine,ses.program,ses.state,ses.status,
decode(d.type,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distrib Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
d.type) lock_type,
decode(d.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(d.lmode)) mode_held
from v\$locked_object loc,v\$session ses,dba_objects obj,v\$lock d
where loc.object_id=obj.object_id
and loc.session_id=ses.sid
and obj.object_id=d.id1
and ses.sid=d.sid
order by oracle_username,seconds_in_wait desc
;
set head off
SELECT 'There are also '||count(*)||' transaction locks'
FROM v\$transaction_enqueue
;
prompt
prompt Blocking Session Details
select BLOCKING_SESSION ||' IS BLOCKING '||sid||','||serial# from v\$session where blocking_session is not null;
exit
EOF
指令碼執行的結果如下:
Current Locks
-------------
SID_SERIAL ORACLE_USERN OBJECT_NAME LOGON_TIM SEC_WAIT OSUSER MACHINE PROGRAM STATE STATUS LOCK_ MODE_HELD
------------ ------------ ------------------------- --------- --------- ---------- ------------ -------------------- ---------- ---------- ----- ----------
5480,2261 PRODUSER TMP_CONTROL 04-DEC-14 419010 mwrk01 fromClient1 JDBC Thin Client WAITING INACTIVE DML Row-X (SX)
5480,2261 PRODUSER TMP_CONTROL 04-DEC-14 419010 mwrk01 fromClient1 JDBC Thin Client WAITING INACTIVE DML Row-X (SX)
4086,35783 PRODUSER TMP_CONTROL 08-DEC-14 44446 mwrk01 fromClient1 JDBC Thin Client WAITING INACTIVE DML Row-X (SX)
4086,35783 PRODUSER TMP_CONTROL 08-DEC-14 44446 mwrk01 fromClient1 JDBC Thin Client WAITING INACTIVE DML Row-X (SX)
7190,24855 PRODUSER TMP_CONTROL 09-DEC-14 8601 mwrk01 fromClient1 JDBC Thin Client WAITING INACTIVE DML Row-X (SX)
7190,24855 PRODUSER TMP_CONTROL 09-DEC-14 8601 mwrk01 fromClient2 JDBC Thin Client WAITING INACTIVE DML Row-X (SX)
8322,17323 PRODUSER BILL_SUMMARY 09-DEC-14 1597 lwrk01 fromClient2 sqlplus@ccbdbpr1 (TNS V1-V3) WAITING ACTIVE DML Row-X (SX)
5179,65435 PRODUSER TMP_BULK_LOCKING 09-DEC-14 296 mwrk01 fromClient2 JDBC Thin Client WAITING INACTIVE DML Row-X (SX)
5179,65435 PRODUSER TMP_BULK_LOCKING 09-DEC-14 296 mwrk01 fromClient2 JDBC Thin Client WAITING INACTIVE TO Row-X (SX)
5179,65435 PRODUSER TEMP_DAEMON_LOCKING 09-DEC-14 296 mwrk01 fromClient2 JDBC Thin Client WAITING INACTIVE DML Row-X (SX)
5179,65435 PRODUSER TMP_BULK_LOCKING 09-DEC-14 296 mwrk01 fromClient2 JDBC Thin Client WAITING INACTIVE TO Row-X (SX)
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/23718752/viewspace-1362396/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 透過shell指令碼檢視鎖資訊指令碼
- 通過shell指令碼檢視procedure的資訊指令碼
- 通過shell指令碼檢視package的資訊指令碼Package
- 透過shell指令碼檢視procedure的資訊指令碼
- 透過shell指令碼檢視package的資訊指令碼Package
- 通過shell指令碼檢測MySQL服務資訊指令碼MySql
- Shell指令碼 – 檢視網路介面資訊指令碼
- 檢視鎖定的session資訊指令碼Session指令碼
- 《通過指令碼檢視哪些ip被佔用》shell筆記指令碼筆記
- 通過shell指令碼來檢視Undo中資源消耗高的sql指令碼SQL
- 通過shell指令碼得到資料字典的資訊指令碼
- 通過shell指令碼檢視資料庫表空間使用情況指令碼資料庫
- 檢視過去的session鎖情況指令碼Session指令碼
- 通過shell指令碼分析足彩指令碼
- 通過shell指令碼得到資料庫的基本資訊(一)指令碼資料庫
- Laravel 通過遷移指令碼建立MySQL檢視Laravel指令碼MySql
- 通過shell定製ash指令碼指令碼
- 通過shell指令碼生成資料統計資訊的報表指令碼
- 檢視備份資訊指令碼指令碼
- [ Shell ] 通過 Shell 指令碼匯出 CDL 網表指令碼
- 【資訊採集】IBM AIX系統硬體資訊檢視命令(shell指令碼)IBMAI指令碼
- 通過shell指令碼防止埠掃描指令碼
- 通過shell指令碼監控oracle session指令碼OracleSession
- [ Shell ] 通過 Shell 指令碼匯出 GDSII/OASIS 檔案指令碼
- 透過shell指令碼來檢視Undo中資源消耗高的sql指令碼SQL
- 通過shell指令碼 批量新增使用者指令碼
- 通過shell指令碼新增備庫日誌指令碼
- 通過shell指令碼來統計段大小指令碼
- mysql鎖以及鎖資訊檢視MySql
- 透過shell指令碼檢視資料庫表空間使用情況指令碼資料庫
- 透過shell指令碼得到資料字典的資訊指令碼
- 【shell 指令碼】檢視*.gz 檔案的內容指令碼
- 通過shell指令碼批量操作mysql資料庫指令碼MySql資料庫
- 如何通過簡單的shell指令碼操作MongoDB指令碼MongoDB
- 通過shell指令碼快速定位active session問題指令碼Session
- Oracle 通過undo塊檢視事務資訊Oracle
- 檢視錶的統計資訊SQL指令碼SQL指令碼
- 通過shell指令碼監控sql執行頻率指令碼SQL