Comments 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 18.104.22.168 to 22.214.171.124 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.
Relo said...Does anyone know if utlrp.sql will recompile Java code as well? I can't find a definitive answer anywhere.
I would suggest you create a Java object, invalidate it, then run the script and see what happens!
Luis said...dbms_utility.compile_schema(schema => 'SCOTT'); invalidates all objects before compiling them. If there are lots of objects it will be a problem.
Better to use dbms_utility.compile_schema(schema => 'SCOTT', compile_all => false);
Good spot. I've amended the article.
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!
If you post personal information (name, email address etc.) you are agreeing to them being stored and displayed. Feel free to remain anonymous.