Solved

Unable to drop a pl/sql package

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

  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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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 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.

744 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

16 Experts available now in Live!

Get 1:1 Help Now