Solved

Unable to drop a pl/sql package

Posted on 2002-07-15
2
5,400 Views
Last Modified: 2008-03-17
I set off a pl/sql package through oracle apps 11i on a 9i database and terminated it after a couple of minutes.
I then edited the program and went to re-compile it but the compilation just hangs.
Even trying to drop the package fails.

Where can I find out what is hanging onto this package which is preventing it from being re-compiuled or dropped?

Carl
0
Comment
Question by:fireword
2 Comments
 
LVL 47

Accepted Solution

by:
schwertner earned 200 total points
ID: 7153995

  Trying to recreate a package, procedure or function (CREATE OR REPLACE ...)
or dropping a the same (DROP PACKAGE ...) causes the application to hang
(ie: SQL*Plus hangs after submitting the statement). Eventually ORA-4021 errors
occur after the timeout (usually 5 minutes).

Error:  ORA 4021
Text:   time-out occurred while waiting to lock object <name>
-----------------------------------------------------------------------------
Cause:  While trying to lock a library object, a time-out occurred.
Action: Retry the operation later.

Solution Description
--------------------

  Verify that the package is not locked by another user by selecting from
V$ACCESS view.  To do this, run:

SELECT * FROM v$access WHERE object = '<PKG>';

Where <PKG> is the package name (usually in all uppercase).  If there is a row
returned, then the package is already locked and cannot be dropped until the
lock is released.  Returned from the query above will be the SID that has this
locked. You can then use this to find out which session has obtained the lock.

  In some cases, that session might have been killed and will not show up.  If
this happens, the lock will not be release immediately.  Waiting for PMON to
clean up the lock might take some time. The fastest way to clean up the lock
is to recycle the database instance.

  If an ORA-4021 error is not returned and the command continues to hang after
issuing the CREATE OR REPLACE or DROP statment, you will need to do further
analysis see where the hang is occuring. A starting point is to have a
look in v$session_wait, see the referenced NOTE.61552.1 for how to analyze hang
situations in general

Solution Explanation
--------------------

Consider the following example:

Session 1:

create or replace procedure lockit(secs in number) as
shuttime date;
begin
  shuttime := sysdate + secs/(24*60*60);
  while sysdate <= shuttime loop
     null;
  end loop;
end;
/
show err

begin
-- wait 10 minutes
  lockit(600);
end;
/              

Session 2:

SQL> create or replace procedure ops$hnapel.lockit as
  2  begin
  3     null;
  4  end;
  5  /          

Result: hang and eventually (the timeout is 5 minutes):

create or replace procedure ops$hnapel.lockit as
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object OPS$HNAPEL.LOCKIT

Session 3:

SQL> connect  / as sysdba
Connected.
SQL> col owner for a10
SQL> col object for a15
SQL> select * from v$access where object = 'LOCKIT';

       SID OWNER      OBJECT          TYPE
---------- ---------- --------------- ------------------------
         9 OPS$HNAPEL LOCKIT          PROCEDURE    

SQL> select sid, event from v$session_wait;

       SID EVENT
---------- ----------------------------------------------------------------
         9 null event
...
        12 library cache pin

As you can see, the blocking sid 9 waits for nothing while session 2, the
hanging session, is waiting for event library cache pin.


0
 

Author Comment

by:fireword
ID: 7154225
thanks for that, the situation resolved itself in the end, presumably the lock released itself after a period of time.
The DBA's couldn't find anything obvious locking the package hence the question being put on here.

I'll add your solution to my "useful things to know that only crop up once in a while" pile.

Carl

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now