Forums | Aggregator | Books | Industry News | Firefox Plugins | Social | Links

Comments

Recompiling Invalid Schema Objects - This article presents several methods for recompiling invalid schema objects.



Mohammad said...

Nice note.

Navin said...

Very good information

Raoul said...

Don't forget that you need SYSDBA role to use UTL_RECOMP scripts !

thushan said...

Shouldn't it be: DBMS_DDL.ALTER_COMPILE?

Surely you can't run DBMS_DDL('PACKAGE', ....)?

It would be DBMS_DDL.ALTER_COMPILE('PACKAGE', ....)

Tim... said...

Yes, you are correct. Dodgy typing on my part. Thanks for the heads-up.

Cheers

Tim...

Harshad said...

Nice and neat explaination. Good Work !!!

srinivas said...

every article is excellent. Thank you verymuch for the great great support.

Brooks said...

What version did utlprp come with? I just upgraded from 8.1.7.4 to 9.2.0.8 and I don't have it.

Tim... said...

It's utlrp.sql, not utlprp.sql :)

Not sure what version it first shipped with, but it is definitely included in 9i.

Cheers

Tim...

jbo5112 said...

In your "Custom Script" you test "IF cur_rec.object_type = 'PACKAGE' THEN". It looks like you just run COMPILE on the package if the object_type is PACKAGE, and if it's not a PACKAGE you run 'ALTER PACKAGE ... COMPILE BODY'. Unless I'm reading it wrongly, you should be testing "cur_rec.object_type != 'PACKAGE'".

Tim... said...

Hi.

The logic looks fine to me:

If the object type is 'PACKAGE' I compile the package. If it is not it must be a 'PACKAGE BODY', so I compile the package body.

Don't understand the problem you have with this. :)

Cheers

Tim...

Adam said...

BE AWARE OF(Notes from the script!):

Rem NOTES
Rem * You must be connected AS SYSDBA to run this script.
Rem * There should be no other DDL on the database while running the
Rem script. Not following this recommendation may lead to deadlocks.

ashok kumar said...

very useful information

thanks for imploding

Sebastián said...

pretty useful

TheGooch said...

Can applications be accessing the data when objects are recompiled? if not, what is a good way to prevent applications from accessing the DB during this operation?

Tim... said...

Hi.

Sure. When you call a procedure/function/package that is invalid Oracle will attempt to re-validate it automatically. Having some objects invalid does not lock out access to anything.

Cheers

Tim...

PATCHANUN said...

THANK YOU , VERY GOOD SCRIPT KA.

DO NOT ask technical questions here! They will be deleted!

These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!

Add your comments here.
Name
Comment
(max 400 chars - plain text)