Home » RDBMS Server » Server Administration » Invalid Objects after CPU PAtch (Oracle 9.2.0.8 AIX)
Invalid Objects after CPU PAtch [message #405455] Wed, 27 May 2009 22:18 Go to next message
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

Re: Invalid Objects after CPU PAtch [message #405456 is a reply to message #405455] Wed, 27 May 2009 22:30 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
ALTER PROCEDURE MY_PROCEDURE COMPILE;
Re: Invalid Objects after CPU PAtch [message #405470 is a reply to message #405455] Wed, 27 May 2009 23:41 Go to previous messageGo to next message
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.
Re: Invalid Objects after CPU PAtch [message #405474 is a reply to message #405455] Wed, 27 May 2009 23:47 Go to previous message
BlackSwan
Messages: 25046
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.
Previous Topic: Add 2nd listener to same host
Next Topic: Oracle Database Diconnection with VC Application (merged 2)
Goto Forum:
  


Current Time: Fri Dec 09 02:26:00 CST 2016

Total time taken to generate the page: 0.09977 seconds