Re: RE: package compilation hangs

From: Raj Jamadagni <rjamya_at_yahoo.com>
Date: Wed, 1 Sep 2004 04:09:58 -0700 (PDT)
Message-ID: <20040901110958.68686.qmail@web50009.mail.yahoo.com>

http://www.ixora.com.au/scripts/sql/executing_packages.sql is what you are looking for. Plsql code cannot be compiled if it is being executed. This sql will list all the SIDs that are executing the code in question.

Once the sessions listed are taken care of, you should be fine.

Raj

jaysingh1_at_optonline.net wrote:

> Gogala,
>
> Thanks for your immediate response.
>
> <<SELECT * FROM V$SESSION_WAIT where sid= 667 >>
>
> If I kill sid 667, some other session appears in DBA_DDL_LOCKS table for that package.
>
> V$SESSION_WAIT says that it is waiting fro "library cache pin"
>
> We cannot restart the db. It is 24/7 but OPS environment.
>
> Thanks
> Jay
>
> ----- Original Message -----
> From: "Gogala, Mladen" <Mladen.Gogala_at_aetn.com>
> Date: Tuesday, August 31, 2004 3:54 pm
> Subject: RE: package compilation hangs
>
> > Did you check V$SESSION_WAIT to see what is it waiting for?
> > It will give you exactly the lock you are waiting for.
> > Kill the session holding the lock and re-try. If that
> > doesn't help, connect as sysdba and type
> > the following:
> > STARTUP FORCE RESTRICT
> > That will almost guarantee that you can recompile the package.
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>

Best Regards
Raj

select mandatory_disclaimer from company_requirements;

Do you Yahoo!?
Win 1 of 4,000 free domain names from Yahoo! Enter now. http://promotions.yahoo.com/goldrush

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Sep 01 2004 - 06:09:29 CDT