Skip to Main Content
  • Questions
  • Is there any command to Unlock a table !

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, OP.

Asked: November 30, 2000 - 9:20 pm UTC

Last updated: March 17, 2004 - 7:23 am UTC

Version: 8.1.6.0.0

Viewed 10K+ times! This question is

You Asked

Dear Tom,
Hi !

Thanks very much for the very useful tips on tuning process.


My Quest to you is

When I am trying to delete some rows from a table T1
it says "TABLE LOCKED".It doesn't allow me to make
deletion possible.

I am logged in to oracle as user "TEST/TEST".There are
so many developers who are also logged in as TEST/TEST.

1) I there any way to unlock this table ?

2) Is there any way to find out how many users are using
T1 table and for what purpose.

3) How to kill sessions in ORACLE for a USER !


Kindly answer !

Thanks and Regards
OP


and Tom said...

1) We don't have an error message "table locked" -- a delete would wait for rows to become unlocked to proceed. I'd really need more information to help you out such as the exact error you are getting and the environment in which you get it (sqlplus, custom app, forms, etc etc etc)

2) select a.username us, a.osuser os, a.sid, a.serial#
from v$session a, v$locked_object b, dba_objects c
where upper(c.object_name) = upper('&tbl_name')
and b.object_id = c.object_id
and a.sid = b.session_id;

will show you who has locks on the object

3) alter system kill session 'sid,serial#'

where you get the sid and serial# from v$session.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

unlock table

anand, March 17, 2004 - 12:45 am UTC

how do i release lock on a table without killing that session

Tom Kyte
March 17, 2004 - 7:23 am UTC

you get the person who owns the session to type in "commit" or "rollback"

that is the only (other) way.