Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Unable to drop a pl/sql package

Posted on 2002-07-15
2
Medium Priority
?
6,284 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

636 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