1

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?

  • Are you using Oracle on Windows operating system or on a Linux/Unix operating system? – John aka hot2use Feb 3 at 8:02
  • Linux operating system. We have killed using pid – User12345 Feb 3 at 9:21
  • When you write: We have killed using pid. Does this mean you no longer require a solution/answer? – John aka hot2use Feb 3 at 14:23
  • But I want to know why the locking occurs and how we can find the cause and why its not fetched using v$locked_object – User12345 Feb 4 at 3:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.