lock on the table

for some reason, I have a lock on one table, what do I need to do to unlock this table( there are no DML on this table at the time)

thanks

select * from dba_waiters;
select * from dba_blockers;

(these tables are created by running ?/rdbms/admin/utllockt)

Quote:

---

Originally posted by marist89

(these tables are created by running ?/rdbms/admin/utllockt)

---

I'm sorry, it's ?/rdbms/admin/catblock.sql (although you can still get your information from utllockt, but you would have to look at it and understand what its doing)

jeff,

thanks for the advise. I did shutdown the instance and restart it backup so there is no lock now on the database. but I would like to know more about your advise. If I an the catblock.sql, and now I can view the dba_waiters and dba_blockers tables, what should I look in here. Sorry I wish I still have the lock to have a better understand but can you explain briefly????
thanks

Jeff or others,

do you have any advises on this lock tables???

Kill the session locking the table.

I did kill the session which lock the tables, the session is gone; however, the lock tables still there.

any thoughts???

Quote:

---

Originally posted by mike2000
I did kill the session which lock the tables, the session is gone; however, the lock tables still there.

---

I am not sure what you are trying to tell...some thing like this

You Kill Session X which holds lock on Table Y..after you do
you mean theres no entry in V$Locked_Object on table Y & still you feel theres lock on the table?

I ran this script and yes there are two tables still locked.

SELECT SUBSTR(B.OWNER,1,8) "Owner",
B.OBJECT_TYPE,
SUBSTR(B.OBJECT_NAME,1,18) "Object_name" ,
DECODE(A.LOCKED_MODE,0,'None' ,1,'Null' ,
2,'Row-S',3,'Row-X' ,
4,'Share',5,'S/Row-X',
6,'Exclusive') "Locked_Mode",
A.SESSION_ID "Sess_ID",
SUBSTR(A.ORACLE_USERNAME,1,10) "User_name",
A.OS_USER_NAME "OS_User",
to_char(c.logon_time,'YYYY/MM/DD HH24:MI:SS') "Logon_Time"
FROM V$LOCKED_OBJECT A,DBA_OBJECTS B,v$session c
WHERE A.OBJECT_ID=B.OBJECT_ID
and a.session_id=c.sid
ORDER BY B.OWNER,B.OBJECT_TYPE,B.OBJECT_NAME;

Mike :

The session indeed is there even after you killed...
I mean there might have been huge Transaction going on by the session.
And even if you have Killed it would have been marked for KILL..
coz it has to rollback the TRANSACTION, until then you will see the locks.

no no no

if you kill the session from the O/S side, the process is gone right away and it's not suppose to mark to kill. I got rid of the process from the O/S side but I still see the lock on the table, that's right I don't understand.

Quote:

---

Originally posted by mike2000
no no no

if you kill the session from the O/S side, the process is gone right away and it's not suppose to mark to kill. I got rid of the process from the O/S side but I still see the lock on the table, that's right I don't understand.

---

If the session still has the table locked, I would say that proves your theory wrong.

yeahhh v$locked_objects is a fastest way to solve lock situation....Ask your DBA do it for you....