V$LOCK(zt)

V$LOCK
Lists the locks currently held by the Oracle database server and outstanding requests for a lock or latch. Any row in V$LOCK either has LMODE=0 (indicating it is a request) or REQUEST=0 (indicating it is a held lock).

The $ORACLE_HOME/rdbms/admin/utllockt.sql script. displays, in tree-structured fashion, the sessions in the system that are waiting for locks and the locks that they are waiting for. Using an ad hoc query tool, such as SQLPlus, the script. prints the sessions in the system that are waiting for locks and the corresponding blocking locks.

Run $ORACLE_HOME/rdbms/admin/catblock.sql before running $ORACLE_HOME/rdbms/admin/utllockt.sql to create DBA_BLOCKERS, DBA_DDL_LOCKS, DBA_DML_LOCKS, DBA_KGLLOCK, DBA_LOCK, DBA_LOCK_INTERNAL and DBA_WAITERS.

Column	Datatypes	Description
ADDR	RAW(4)	Address of lock state object.
KADDR	RAW(4)	Address of lock.
SID	NUMBER	Identifier of the session holding/requesting the lock.
TYPE	VARCHAR2(2)	Type of lock.
ID1	NUMBER	Lock resource identifier.
ID2	NUMBER	Lock resource identifier.
LMODE	NUMBER	Lock mode in which the session holds the lock:    0 - none    1 - null (NULL)    2 - row-S (SS)    3 - row-X (SX)    4 - share (S)    5 - S/Row-X (SSX)    6 - exclusive (X)
REQUEST	NUMBER	Lock mode in which the session requests the lock:    0 - none    1 - null (NULL)    2 - row-S (SS)    3 - row-X (SX)    4 - share (S)    5 - S/Row-X (SSX)    6 - exclusive (X)
CTIME	NUMBER	Time since current mode was granted.
BLOCK	NUMBER	The lock is blocking another lock.

Related Parameters:
dml_locks	Specifies the maximum number of DML locks--one for each table modified in a transaction.
enqueue_resources	Sets the number of resources that can be concurrently locked by the lock manager.

Related Parameters:
_enqueue_locks
dml_locks	Specifies the maximum number of DML locks--one for each table modified in a transaction.
enqueue_resources	Sets the number of resources that can be concurrently locked by the lock manager.

Related Commands:
ALTER TABLE DISABLE TABLE LOCK

Related Views:
V$_LOCK
V$ACCESS	Displays objects in the database that are currently locked and the sessions that are accessing them.
V$SESSION
V$ENQUEUE_LOCK	Displays all locks owned by enqueue state objects.
V$ENQUEUE_STAT	Contains detailed enqueue statistics for each enqueue.
V$GLOBAL_BLOCKED_LOCKS	Displays global blocked locks.
V$LOCKED_OBJECT	Lists all locks acquired by every transaction on the system.
V$RESOURCE	Contains resource name and address information
V$RESOURCE_LIMIT	Displays information about current and maximum global resource utilization for some system resources.
V$TRANSACTION_ENQUEUE	Displays locks owned by transaction state objects.
DBA_BLOCKERS	Shows all sessions holding a lock which is required by another process.
DBA_DDL_LOCKS	Lists all DDL locks held in the database and all outstanding requests for a DDL lock.
DBA_DML_LOCKS	Displays a session if it is not waiting for a locked object but is holding a lock on an object for which another session is waiting.
DBA_KGLLOCK	Lists all the locks and pins held on KGL objects (objects in the Kernel Generic Library cache).
DBA_LOCK	All locks held or requested within the database.
DBA_LOCK_INTERNAL	Displays a row for each lock or latch that is being held, and one row for each outstanding request for a lock or latch.
DBA_WAITERS	Shows all sessions waiting for locks and the session that holds the lock.
DBMS_LOCK_ALLOCATED	Describes user-allocated locks.

Related Packages:
DBMS_LOCK	Lock Management services for your PL/SQL applications.

Related Packages:
showBlockinLock.sql	Detect blocking locks.

QuickSQL:

--show blocking locks. Also see sql here

select SID,ADDR,KADDR,type,LMODE,round(CTIME/60) "Time(Min)" 
from V$LOCK where block=1;

       SID ADDR             KADDR            TYPE          LMODE  Time(Min)
---------- ---------------- ---------------- -------- ---------- ----------
       506 00000006D92DC9D0 00000006D92DC9F8 TM                3         46
       867 00000006D93654C8 00000006D9365640 TX                6         23
      1010 00000006D9342608 00000006D9342780 TX                6          5
      2075 00000006D82A0188 00000006D82A0300 TX                6         13

--show blocking locks.

select sid,username from V$SESSION where sid in 
(select sid from V$LOCK where block=1);

       SID USERNAME
---------- --------------------
       506 LBRAUN
       867 DKANE
      1010 WWW_USER1
      2075 WWW_USER1

--generate sql to disconnect blocking users.

select 'alter system disconnect session '''||sid||','||serial#||'''immediate;' 

from V$SESSION 
where sid in (select sid from V$LOCK where block=1);

'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||''';'
--------------------------------------------------------------------------------------------------------------
alter system disconnect session '51,506' immediate;
alter system disconnect session '362,867' immediate;
alter system disconnect session '867,1010' immediate;
alter system disconnect session '1010,2075' immediate;

--show locks.

select b.sid,b.username,d.id1,a.sql_text from V$SESSION b,V$LOCK d,V$SQLTEXT a
where b.lockwait = d.kaddr and   a.address = b.sql_address and   
a.hash_value = b.sql_hash_value;

--show locking users.

select a.sid,a.username,b.id1,c.sql_text from V$SESSION a,V$LOCK b,V$SQLTEXT c
where b.id1 in (select distinct e.id1 
from V$SESSION d, V$LOCK e where d.lockwait = e.kaddr) 
and a.sid = b.sid and 
c.hash_value = a.sql_hash_value and b.request = 0;

--show long operations still in progress

select b.sid,b.username,d.id1,a.sql_text from V$SESSION b,V$LOCK d,V$SQLTEXT a 
where b.lockwait = d.kaddr
and a.address = b.sql_address and a.hash_value = b.sql_hash_value;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/756652/viewspace-438697/，如需轉載，請註明出處，否則將追究法律責任。