[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Unable to drop a pl/sql package

Posted on 2002-07-15
2
Medium Priority
?
6,434 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 48

Accepted Solution

by:
schwertner earned 600 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.


1
 

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses
Course of the Month18 days, 8 hours left to enroll

826 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