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.