CODEWITHSUNDEEP
oracle-databasetoadlocks
Martin Gramatica
Martin Gramatica

Reputation: 75

Can not compile a stored procedure because it seems to be locked

I am working with a stored procedure in Oracle. I was debugging it and for some reason, my Toad hanged and I had to end the process. As a result, if I want to re-compile the stored procedure, it takes for ever just like when an object is locked.

I tried finding locks using the sysadmin, but I am not being able to find the right query to get the lock. I want to kill the process that is locking the stored procedure .

Upvotes: 0

Views: 1677

Answers (1)

Marcus
Marcus

Reputation: 3869

Check the status to confirm your session is still running which is not allowing you to recompile procedure:

SELECT sid, serial#, status FROM v$session;

Execute the below script which will print out sqls, which should be executed. 

SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' 
FROM v$session 
where username='YOUR_USER';

Note: If you want to kill all the sessions,the above script will prepare all small scripts.

Then try to re-compile your stored procedure.

Upvotes: 1

Related Questions