Link to home
Start Free TrialLog in
Avatar of GouthamAnand
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.
ASKER CERTIFIED SOLUTION
Avatar of MikeOM_DBA
MikeOM_DBA
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sean Stuber
Sean Stuber

You don't need sysdba to track locks.
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.
A package being used does not create a "lock" on itself.
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

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;

Open in new window


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.
Avatar of GouthamAnand

ASKER

Thank you.