|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.
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 220.127.116.11 to 18.104.22.168 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.
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'".
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. :)
Adam said...BE AWARE OF(Notes from the script!):
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?
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.
PATCHANUN said...THANK YOU , VERY GOOD SCRIPT KA.