pl/sql package locked, but can't find it

[pwoneill]

Hey guys,

A user is complaining that he can't compile his package because something has it locked. First he tried to compile with TOAD, which just freezes and eventually times out. Then in Sqlplus, he got the message that it timed out waiting for a lock.

There are no records with the package or package body's object_id in dba_lock, v$lock, v$locked_object.

I'm sure I could just bounce the db since it's development but would rather try to find the solution

He's not running the procedure, just compiling, so there shouldn't be a problem with other objects (tables referenced in the procedure, etc) correct?

Any ideas?

[jdorlon]

try

select * from v$session where status = 'ACTIVE'

maybe it will give you a clue...

If a procedure is being debugged with DBMS_DEBUG, I don't
think you can compile it. If this is the case, I think you'll find
it with that query.

And tell him to use EZSQL instead of TOAD! ;-)

-John
http://www.ezsql.net

[lesstjm]

I had the same problem a couple of weeks ago. The answers I got back are at http://www.dbasupport.com/forums/sho...threadid=10973

What happened was that I ran the procedure and it was taking forever so I killed my sqlplus. Then later when I tried to compile it again it just sat there and then timed out. The first time I ran it and killed SQLPlus it was still running. SO the developer that is trying to compile probably still has the procedure locked. I had to go into v$session it showed what I had locked. Then I killed it through enterprise manager. It turned out that I had an endless loop in my procedure. I didn't intialize one of my variables. That is probably what is happening to you.