Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE:batch recompile invalid packages
One simple but tedious way is ,
alter package .... compile ; ( also 'compile body ' , for compiling the body )
You can prepare a script which contain such alter commands for all packages . The script can also be prepared by ,
SELECT 'alter package ' || OBJECT_NAME || ' compile ; ' FROM ALL_OBJECTS WHERE OBJECT_TYPE = 'PACKAGE' AND STATUS='INVALID'; SELECT 'alter package ' || OBJECT_NAME || ' compile body ; ' FROM ALL_OBJECTS WHERE OBJECT_TYPE = 'PACKAGE BODY' AND STATUS='INVALID'; Spool the output of above in a file ( say xxx.sql and run this xxx.sql file ) .
In recent versions of Oracle there is package , DBMS_UTILITY . With the procedure , DBMS_UTILITY.COMPILE_SCHEMA(schema_name) , will compile the store objects .
Cheers ,