lock on the table

[mike2000]

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

[marist89]

select * from dba_waiters;
select * from dba_blockers;

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

[marist89]

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)

[mike2000]

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

[mike2000]

Jeff or others,

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

[abhaysk]

Kill the session locking the table.

[mike2000]

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

any thoughts???

[abhaysk]

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?

[mike2000]

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;

[abhaysk]

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.