GouthamAnand
asked on
unable to compile oracle package
Hi,
When I try to compile oracle package, its keep on compiling and after that session is getting hanged in Toad.
Also this package is not currently executing. I do not have sysdba privileges on this database.
Can you please let me know is there any way to check which is blocking this package and kill that process and compile the package?
Thanks.
When I try to compile oracle package, its keep on compiling and after that session is getting hanged in Toad.
Also this package is not currently executing. I do not have sysdba privileges on this database.
Can you please let me know is there any way to check which is blocking this package and kill that process and compile the package?
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
A package being used does not create a "lock" on itself.
Oracle prevents packages loaded in Shared pool from being changed (compiled).
Oracle prevents packages loaded in Shared pool from being changed (compiled).
while the package is executing, that session will have a lock.
However, I was incorrect earlier when I suggested using Toad's lock tab.
The locks are visible in dba_lock but Toad's browser doesn't pick them up.
Instead, simply check v$session and you can find the blocked session and its blocker easily
select sid,blocking_session from v$session where event = 'library cache pin';
It's easy to test and confirm
Then in one session
exec lock_test.dummy_loop;
In a second session try compiling the package while the first session is still running
alter package lock_test compile;
In a third session look for the lock while the other two are executing
select sid,blocking_session from v$session where event = 'library cache pin';
However, I was incorrect earlier when I suggested using Toad's lock tab.
The locks are visible in dba_lock but Toad's browser doesn't pick them up.
Instead, simply check v$session and you can find the blocked session and its blocker easily
select sid,blocking_session from v$session where event = 'library cache pin';
It's easy to test and confirm
CREATE OR REPLACE PACKAGE lock_test
IS
PROCEDURE dummy_loop;
END;
CREATE OR REPLACE PACKAGE BODY lock_test
IS
PROCEDURE dummy_loop
IS
v_stop DATE := SYSDATE + 1 / 1440;
BEGIN
-- don't do loops like these in production code, use a real "sleep"
-- for one time test though, this should be ok.
WHILE SYSDATE < v_stop
LOOP
NULL;
END LOOP;
END;
END;
Then in one session
exec lock_test.dummy_loop;
In a second session try compiling the package while the first session is still running
alter package lock_test compile;
In a third session look for the lock while the other two are executing
select sid,blocking_session from v$session where event = 'library cache pin';
Note, if your package touches other objects, you might still be able to see the session in the locks tab, but it won't be the locks on the library cache.
ASKER
Thank you.
Since you're using Toad, the easiest way to detect the locks is to go the session browser and click on the "Locks" tab.
Look in the "blocking" and "blocked" columns.
If you don't have privileges to see the locks then you will still need a DBA to help you; but not "sysdba" level privileges.