Sunday, October 5, 2008

How to find a Lock on any Object?

Hi Friends,

Yesterday Arun was asking about how to find a lock on the objects.

When an object is locked by any other user oracle automatically udpates the view V$Locked_Objects in SYS Schema

For example:

1. Open Sql-prompt and connect it with Scott User.

2. Update EMP table.
SQL> update emp set sal=sal+100;
3. Open Sql-prompt and connect it with SYS User.

4. Query the view V$Locked_Objects

SQL> select * from v$locked_object;

    XIDUSN    XIDSLOT     XIDSQN  OBJECT_ID SESSION_ID ORACLE_USERNAME                OS_USER_NAME           PROCESS      LOCKED_MODE
---------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------ ------------ -----------
        10          6        983      30139         10 SCOTT                          IMERGE\ingress              476:5612                   3
5. Here you can see Oracle_Username with its Session ID and other details.


Hope this will help you out. Please give comments.


No comments:

Post a Comment