在生产环境维护中，如果碰到较长时间的锁等待，很可能会造成各种可能的问题。我们可以使用如下的脚本来实时监控锁的情况。
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)