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.
GouthamAnandAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MikeOM_DBACommented:
. . .  I do not have sysdba privileges on this database.
Then you need someone that does.
Some process has executed and loaded that package into the shared pool.
You need someone to flush the shared pool.
:p

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdstuberCommented:
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.
MikeOM_DBACommented:
A package being used does not create a "lock" on itself.
Oracle prevents packages loaded in Shared pool from being changed (compiled).
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

sdstuberCommented:
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';
sdstuberCommented:
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.
GouthamAnandAuthor Commented:
Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.