How To View Existing Locks on the Database?

As can see from the pervious tutorial exercise, performance of the second session is greatly
affected by the data lock created on the database. To maintain a good performance level for all
sessions, you need to monitor the number of data locks on the database, and how long do they
last.
Oracle maintains current existing data locks in a Dynamic Performance View called V$LOCK with
columns like:
SID - Session ID to identify the session that owns this lock.
TYPE - The type of the lock, like TM (DML enqueue) and TX (Transaction enqueue).
LMODE - The lock mode in which the session holds the lock.
REQUEST - The lock mode in which the session requests the lock.
CTIME - The time since current lock mode was granted
BLOCK - A value of either 0 or 1, depending on whether or not the lock in question is the blocker.
The following tutorial exercise shows you how to view existing locks on the database:
(session 1)
SQL> connect HR/fyicenter
SQL> UPDATE fyi_links
SET url='centerfyi.com'
WHERE id=110;
1 row updated.
(session 2)
SQL> connect HR/fyicenter
SQL> INSERT INTO fyi_links
(url, id) VALUES
('oracle.com', 112);
1 row created.
SQL> UPDATE fyi_links
SET notes='FYI Resource'
WHERE id=110;
(wait for lock on row id=110)
Now keep those two sessions as is. You need to open a third window to connect to the database
as SYSTEM to view all current locks:
(session 3)
SQL> connect SYSTEM/password
SQL> select sid, username from v$session where username='HR';
SID USERNAME
---------- ------------------------------
23 HR
39 HR
SQL> SELECT sid, type, lmode, request, ctime, block
FROM V$LOCK WHERE sid in (23, 39) ORDER BY ctime DESC;
SID TY LMODE REQUEST CTIME BLOCK
---- -- ---------- ---------- ---------- ----------
1 39 TX 6 0 84 1
2 39 TM 3 0 84 0
3 23 TM 3 0 27 0
4 23 TX 6 0 27 0
5 23 TX 0 6 18 0
You should read the output as:
Line #1 and #2 represent the lock resulted from the UPDATE statement in session #1 on row
id=110.
Line #3 and #4 represent the lock resulted from the INSERT statement in session #2 on row
id=112.
Line #5 represents a request of lock resulted from the UPDATE statement in session #2 on row
id=110, which is blocked by the lock from line #1 and #2.