RE: package compilation hangs

  • From: "Powell, Mark D" <mark.powell@xxxxxxx>
  • To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 31 Aug 2004 16:12:12 -0400

Jay, to compile a package Oracle takes an exclusive lock on the package.
Oracle cannot get the exclusive lock if the package is in use.  You should
generally not make changes to tables referenced in stored code that itself
is referenced by other stored code except during periods of very low usage
or maintenance windows.

If you must make the change during busy periods you should try to stop or
get the users out of the affected applications at the time of the change.
Then when you go to recompile invalided packages only recompile the body if
the specification was not changed since recompiling the specification can
cause cascading invalidations.

If you want long enough the re-compile may well work, but you might try
canceling it and issuing just a recompile on the body if you made this
mistake.

alter package x compile [spec and body] verse alter package x compile body
[to just do the body and limit cascading if the spec was not changed] 

HTH -- Mark D Powell --


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of
jaysingh1@xxxxxxxxxxxxx
Sent: Tuesday, August 31, 2004 3:49 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: package compilation hangs


Hi All,

The package BODY status became invalid because of the object
modification(another package) refered in the package body.

Now we are trying to compile the package and it hangs.

Checked DBA_DDL_LOCKS 
select owner,session_id,mode_held from dba_ddl_locks where
name='SEARCH_PKG';

OWNER,SESSION_ID,MODE_HELD
----- ---------- ----------
REPORTS,667,Share
PUBLIC,667,Share
ATG56,667,Share
REPORTS,667,None
REPORTS,968,Exclusive


If I kill session_id 968, something else appears here. I did this exercise 4
times.
What could be wrong here.


select * from gv$access where object='SEARCH_PKG' is returning more than 50
records.

Could someone shed some light on this?

Thanks
Jay




----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: