Many a times we face some issue, that a perticular sql query is holding a lock(s) on certain object. This will impact concurrency issue also for other DMLs. Some other user may complaint that he is using a DML query(Insert/delete/update) and qurey is taking lot of time. One of the reason for this might be that, the object which 2nd query wants to use is already locked by first query. In Oracle database 10g we can check the objects, which are locked by any other user. We have a table called V$LOCKED_OBJECTS, which lists all locks acquired by every transaction on the system.
Example, lets say a user is holding lock on certain table by some DML query from long time. As a DBA you want to find out the session and kill the same so that other can use the resource. Following steps can be followed to get this info and remove the lock.
SQL> select object_id, session_id, oracle_username, os_user_name, process from v$locked_object
2 ;
OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS
———- ———– ———————- —————– ——–
1991774 368 APPS plnops 26188
2019121 368 APPS plnops 26188
34376 507 APPS plnops 25922
Based on the above output you can check the object, which the user has locked. Example, if we take a object ID 1991774, then following query will give us the output as object_name.
SQL> select object_name from dba_objects
2 where object_id = 1991774;
OBJECT_NAME
——————————————————————————–
MSC_ST_DEMANDS
Also from SESSION_ID value we can get the session details of the user. Lets check for SESSION_ID 368. Here the SESSION_ID column of V$LOCKED_OBJECT is same as SID column of V$SESSION.
SQL> select SID, SERIAL#, sql_id from v$session
2 where SID=368;
SID SERIAL# SQL_ID
———- ———- ————-
368 545 ag47457n3xck1
From the SQL_ID we can easily get the SQL query, the user is using as shown below.
SQL> select sql_text from v$sql
2 where sql_id = ‘ag47457n3xck1’;
SQL_TEXT
——————————————————————————–
insert into MSC_ST_DEMANDS ( INVENTORY_ITEM_ID, SOURCE_INVENTORY_ITEM_ID,
ORGANIZATION_ID, WIP_ENTITY_ID, SOURCE_WIP_ENTITY_ID, ORDER_NUMBER,
WIP_STATUS_CODE, WIP_SUPPLY_TYPE, OPERATION_SEQ_NUM, USING_REQUIREMENT
_QUANTITY, QUANTITY_ISSUED, USING_ASSEMBLY_ITEM_ID, DEMAND_TYPE, PRO
JECT_ID, TASK_ID, PLANNING_GROUP, END_ITEM_UNIT_NUMBER, DEMAND_CLASS
, ORIGINATION_TYPE, USING_ASSEMBLY_DEMAND_DATE, MPS_DATE_REQUIRED, D
ELETED_FLAG, QUANTITY_PER_ASSEMBLY, ASSET_ITEM_ID, ASSET_SERIAL_NUMBER,
COMPONENT_SCALING_TYPE, COMPONENT_YIELD_FACTOR, REFRESH_ID, SR_INST
ANCE_ID) select x.INVENTORY_ITEM_ID, x.INVENTORY_ITEM_ID, x.ORGANIZATI
ON_ID, x.WIP_ENTITY_ID, x.WIP_ENTITY_ID, x.WIP_ENTITY_NAME, x.STATU
S_CODE, x.WIP_SUPPLY_TYPE, x.COPY_OP_SEQ_NUM, x.NEW_REQUIRED_QUANTITY,
SQL_TEXT
——————————————————————————–
x.QUANTITY_ISSUED, x.JOB_REFERENCE_ITEM_ID, x.DEMAND_TYPE, x.PROJECT_I
D, x.TASK_ID, x.PLANNING_GROUP,
The above query gives first 1000 characters of SQL query. To get the complete SQL you can check the column SQL_FULLTEXT in same table(V$SQL).
Once you get the session information for SQL, you can kill the session using following commands.
SQL> alter system kill session ‘368, 545’;
System altered.
Check performance after killing 1 session. Usually the performance improves and other users will appriciate you, as there query will run faster. (:-)).
Persistent Storage Solutions 