User registered ORA-00054 (Object Lock) issue while dropping a table.
I checked the object lock on database and session details using that object.
select c.owner, c.object_name, c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine from v$locked_object a, v$session b, dba_objects c where b.sid = a.session_id and a.object_id = c.object_id; -- SELECT DISTINCT t2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session t2 where t1.session_id=t2.sid order by t2.logon_time;
Now that I have session and serial#, I tried to kill that session to release the object, but I got ORA-00030.
Generally ORA-00030 comes when user id is not exist in connected session as user may be disconnected, logged out by him/her self or you provide the wrong SID. But sometimes when you kill a process it remains in KILLED state but locked resources are not released for a long time, in that case also you may receive ORA-00030.
Similarly I can can the user is active is v$session, but user confirmed he was logged out many hours prior, there was nothing on the rollback segment as well.
SELECT Username,SID,SERIAL#,STATUS,action from v$session where username is not null;
Now in this case I just checked the OS process if this session and killed it at OS level. Once killed, object lock and session detail both were cleared.
select spid,osuser,s.program from v$session s,v$process p where s.paddr=p.addr and s.sid=&sid;
Thankyou for reading.