Menu Close

Getting ‘ORA-00030: User session ID does not exist’ while killing a session

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.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *

error: Content is protected !!