Solved

Unable to drop a pl/sql package

Posted on 2002-07-15
2
5,490 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

815 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

10 Experts available now in Live!

Get 1:1 Help Now