Steps To Detect And Remove Locks

Wheels India Ltd. DATABAS E I N FO R MATI O N Steps to detect Locks In Wheels India the table level lock occurs mostly between PURCHASE, GS, Sales and RMS departments. In order to detected locks we need to query the views v$lock, v$session and dba_objects. A set of sql scripts has been created to detect the locks, they are “LOCK” and “LOCK_OB”. To detect table lock, follow these steps: 1. Execute the Sql script “Lock”. 2. Check if the column “Block” has value 1. If found then it indicates that a lock has occurred. 3. Note the “SID” of the row, which indicates the “Block” column as 1. 4. Execute the Sql script “Lock_ob”. 5. Search for the above SID (noted in step 3), and note the corresponding “Object_name”. 6. There will be another row with the same “Object_name”. 7. Using the above two SID’s, query the user information from v$session. Eg: SQL> @lock ADDR

KADDR

SID

0B6A1268 0B6A1276 0BDFBCED 0BDFBCFF

TY 4 RT 16 TM

ID1

ID2

1 18462

LMODE REQUESTCTIME 0 0

6 6

0 0

BLOCK

79806 74

0 1

The above query indicates a lock has occurred, because the “Block” column indicates value 1, and the corresponding SID is 16. SQL> @lock_ob SID 16 19

SERIAL# USERNAME Lock Level OWNER 160 RADIANT 155 WILDBA

OBJECT_TYPE OBJECT_NAME

Table Lock RADIANT TABLE Table Lock RADIANT TABLE

TEST TEST

The above query indicates that both the SID’s 16,19 uses the same table “Test”. To find the user information corresponding to the above two SID’s enter the following query: SQL>select sid,serial#,username,status,machine,terminal,osuser,program from v$session where sid in(16,19); SID SERIAL# USERNAME 16 19

160 RADIANT 155 WILDBA

STATUS MACHINE

TERMINAL OSUSER PROGRAM

ACTIVE DOMAIN\WILTEST WILTEST ACTIVE DOMAIN\WILTEST WILTEST

Radiant Radiant

SQLPLUSW.EXE SQLPLUSW.EXE