OraFAQ Forum: Server Administration » Invalid Objects after CPU PAtch

Home » RDBMS Server » Server Administration » Invalid Objects after CPU PAtch (Oracle 9.2.0.8 AIX)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Invalid Objects after CPU PAtch [message #405455]

Wed, 27 May 2009 22:18

dbcop
Messages: 37
Registered: September 2006
Location: india

Member

Hi ,

I have applied CPU April 09 patch on a 9.2.0.8 database after which I find there are total 95 objects going in invalid state.
The objects include procedures and view.
The view recompilation script was also executed after the application of the patch but still there many views in invalid status.
I tried to compile them using script utlrp and also tried to manually compile but all of them were in vain.

Actually I have seen this that before executing the cacpu.sql script if I run utlrp and then run catcpu and view recompilation script then the objects does not go in an invalid status but in a particular system I forgot to run utlrp before the patch and now I am into this situation.

Actually the objects were in invalid status prior to patch apply but I should have run utlrp to get them into valid status.

Is there a way to get the objects back to valid status?

I had also tried to compile the procedures using the following which were also in vain

EXEC DBMS_DDL.alter_compile('PROCEDURE', 'MY_SCHEMA', 'MY_PROCEDURE');

Please let me know a way out.

Should I use
EXEC DBMS_UTILITY.compile_schema(schema => 'USER');

Thanks
Dbcop

Report message to a moderator

Re: Invalid Objects after CPU PAtch [message #405456 is a reply to message #405455]

Wed, 27 May 2009 22:30

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

ALTER PROCEDURE MY_PROCEDURE COMPILE;

Report message to a moderator

Re: Invalid Objects after CPU PAtch [message #405470 is a reply to message #405455]

Wed, 27 May 2009 23:41

dbcop
Messages: 37
Registered: September 2006
Location: india

Member

Thanks for reply but I tried it much before .

Of no use
I think the dependent procedures or functions needs to be compiled first.

But it is difficult task to determine thr dependent pls/sql codes for 77 invalid procedures .

Needless to say

alter view viewname compile;
also didn't work.

Recreating view is an option but it would cause a lot of locks on the most used tables and would need a quite time to do so.

So I was just trying to evaluate other possible ways.

Report message to a moderator

Re: Invalid Objects after CPU PAtch [message #405474 is a reply to message #405455]

Wed, 27 May 2009 23:47

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

You make claims that we can not verify or dispute.

Therefore you are left to solve your own problems.

You can write SQL, to write SQL to solve your problem.
Of this, I am sure.

Report message to a moderator

Previous Topic:	Add 2nd listener to same host
Next Topic:	Oracle Database Diconnection with VC Application (merged 2)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jul 02 22:30:32 CDT 2024