Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Compiling Stored Procedure hangs Oracle SQL Developer

I am facing an unusual problem in Oracle SQL Developer. When I try to compile a store procedure it hangs up the Oracle SQL DEveloper Screen and I can't do any activity, except to kill the SQL developer instance and start again.

But the same issue. Anybody faced such problem? I am new to oracle.

Here some additions to the problem I waited for 5 to 10 mins and got this error

ORA-04021 timeout occurred while waiting to lock object

But I am the only person working on this instance of Oracle

like image 424
user367134 Avatar asked Jul 23 '11 11:07

user367134


People also ask

What happens when a stored procedure is compiled?

When a procedure is compiled for the first time or recompiled, the procedure's query plan is optimized for the current state of the database and its objects. If a database undergoes significant changes to its data or structure, recompiling a procedure updates and optimizes the procedure's query plan for those changes.

How do I see compilation errors in SQL Developer?

control-shift-L should open the log(s) for you. this will by default be the messages log, but if you create the item that is creating the error the Compiler Log will show up (for me the box shows up in the bottom middle left).


1 Answers

One of your previous attempts to create the procedure, which you killed, is still stuck and active. You need to kill that Oracle session using http://www.oracle-base.com/articles/misc/KillingOracleSessions.php

The other source of this error is another process in your database is running the procedure while you are trying to compile it. Use this query to figure out which process is running it:

select sess.sid, sess.username, sql_text
from v$sqlarea sqlarea, v$session sess
where sess.sql_hash_value = sqlarea.hash_value
and   sess.sql_address    = sqlarea.address
and   sess.username is not null;
like image 148
Thomas Jones-Low Avatar answered Sep 30 '22 18:09

Thomas Jones-Low