Table Lock in Oracle

The challenge was to find out which OS user, through which DB login and from which machine the table has been locked and the type of lock that has been applied.

Situation: Application performance issue; Table Lock

Solution:  Two options to find out,

1.      Lock query: Sample query and sample output.

select a.object_id, b.object_name, a.session_id, a.oracle_username, a.os_user_name, a.process, a.locked_mode from   sys.V_$LOCKED_OBJECT a, all_objects b where a.object_id = b.object_id

 

2.      TOAD > Session Browser: This will display all the sessions and the query fired by the user and the lock mode on them. 

 

Pre-requisite for doing the above is to have read-only access on V$session and corresponding tables and it works only for Oracle.

Tags