Error: ORA-04021: timeout occurred while waiting to lock object

User_GK50K

Hi,

I was making changes in procedure from sql developer. suddenly it started to give me this error.

Even if I restart connection, I cant make any changes any more. oracle version 12c.

NOTE: I can make changes to other procedure there is no issue.

I have run below queries but the object Id doesn't match the procedure name.

---------------

SELECT ORACLE_USERNAME, OS_USER_NAME,LOCKED_MODE,OBJECT_NAME,OBJECT_TYPE

  FROM V$LOCKED_OBJECT A, DBA_OBJECTS B

  WHERE A.OBJECT_ID=B.OBJECT_ID;

  --------------

SELECT SID, OWNER, OBJECT, TYPE

   FROM V$ACCESS

   WHERE OBJECT = 'gv_$instance';

-------------------------

oracle_dump.txt

There has been same issue before but I dont see any solution here? https://community.oracle.com/tech/developers/discussion/comment/3906357

Thank you for your supports.

User_GK50K

Found Solution walk through link:

https://titanwolf.org/Network/Articles/Article?AID=082bc19d-377c-45d3-9bcc-a3d0b00e640b#gsc.tab=0

sample script:

-----locked procedures

 Select s.SID, s.SERIAL#, p.SPID, s.OSUSER, s.program, q.SQL_TEXT

   From v$session s, v$process p, dba_ddl_locks d, v$sql q

      Where s.paddr = p. addr 

           and d.session_id = s.SID

           and s.SQL_ID = q.SQL_ID

           and d.name = 'PROCEDURE'; ----- specific stored procedure name, capitalized

------Terminate session

ALTER SYSTEM KILL SESSION 'sid,serial#';