Problem solve Get help with specific problems with your technologies, process and projects.

Find locked objects

This script will find which tables in a schema are locked and which session has locked it.

If a table used by an user A is locked by User B then A needs to wait until B unlocks it. By issuing this query, User A can find which tables in his schema are locked and which session has locked it. It has been tested on versions 8 and up.

SQL>select a.sid,a.serial#,c.object_name 
 from V$session a, 
 V$locked_object b, 
 user_objects c 
 where a.sid=b.session_id 
 and b.object_id=c.object_id; 
 

Output:

 SID SERIAL# OBJECT_NAME 
 ---- ------- ------------ 
 7 36 emp 
 9 58 dept 
 
 2 rows selected. 

Now you can release the lock:

 
 SQL>alter system kill session '7,36'; 
 
 System Altered. 
 
 SQL>alter system kill session '9,58'; 
 
 System Altered. 
 
Note: Make sure you know what the sessions are doing before killing them!

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our applications, SQL, database administration, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close