Find locks on a table

Using the step below one can find the locks on the table by looking for object id.

Example:
Window 1:
SQL> update scott.test1 set x = 200;

16 rows updated.

SQL> select userenv(‘sid’) from dual;

USERENV(‘SID’)
————–
19

Window 2:
SQL> select object_id from dba_objects where object_name = ‘TEST1’ and owner = ‘SCOTT’;

OBJECT_ID
———-
13846

SQL> select * FROM V$lock where id1 = 13846;

ADDR KADDR SID TY ID1 ID2 LMODE
—————- —————- ———- — ———- ———- ———-
REQUEST CTIME BLOCK
———- ———- ———-
000000006934A5B0 000000006934A5D8 19 TM 13846 0 3
0 14775 0

Shows SID=19 has lock on the table.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s