I have tried to execute a stored procedure but its running for an hour. So I have checked for locks using below query but it doesn't return anything.
select session_id, a.object_id, xidsqn, oracle_username, b.owner owner, b.object_name object_name, b.object_type object_type FROM v$locked_object a, dba_objects b WHERE xidsqn != 0 and b.object_id = a.object_id;
When tried to recompile the procedure result is same, So I have inspected if there is any compiling locks using below query ,
SELECT s.sid, l.lock_type, l.mode_held, l.mode_requested, l.lock_id1 FROM dba_lock_internal l, v$session s WHERE s.sid = l.session_id AND UPPER(l.lock_id1) LIKE '%SS_ORACLE%' AND l.lock_type = 'Body Definition Lock' order by l.lock_id1;
It also didn't return any results. So I have tried below query and it return an session with ROW EXCLUSIVE lock
SELECT l.inst_id,SUBSTR(L.ORACLE_USERNAME,1,8) ORA_USER, SUBSTR(L.SESSION_ID,1,3) SID, S.serial#, SUBSTR(O.OWNER||'.'||O.OBJECT_NAME,1,40) OBJECT, P.SPID OS_PID, DECODE(L.LOCKED_MODE, 0,'NONE', 1,'NULL', 2,'ROW SHARE', 3,'ROW EXCLUSIVE', 4,'SHARE', 5,'SHARE ROW EXCLUSIVE', 6,'EXCLUSIVE', NULL) LOCK_MODE FROM sys.GV_$LOCKED_OBJECT L, DBA_OBJECTS O, sys.GV_$SESSION S, sys.GV_$PROCESS P WHERE L.OBJECT_ID = O.OBJECT_ID and l.inst_id = s.inst_id AND L.SESSION_ID = S.SID and s.inst_id = p.inst_id AND S.PADDR = P.ADDR(+) order by l.inst_id ;
But when i tried to kill the session I got below error message,
alter system kill session '122,44454' immediate Error report - ORA-00030: User session ID does not exist. 00030. 00000 - "User session ID does not exist." *Cause: The user session ID no longer exists, probably because the session was logged out. *Action: Use a valid session ID.
Please guide how we can kill the session?