透過shell指令碼檢視鎖資訊

在生產環境維護中，如果碰到較長時間的鎖等待，很可能會造成各種可能的問題。我們可以使用如下的指令碼來實時監控鎖的情況。

sqlplus -S $DB_CONN_STR@$SH_DB_SID <<EOF 
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)