Friday, January 25, 2013

Check invalid packages

SELECT * FROM ALL_OBJECTS WHERE STATUS!='VALID' and owner not in ('SYS', 'PUBLIC', 'SYSTEM', 'WMSYS', 'SYSMAN', 'APEX_030200', 'VAS_P2P_DB', 'VAS_INDICATOR_DB', 'VAS_MULTIMSI_DB', 'VAS_PROPERTY_DB', 'VAS_ACK_DB', 'VAS_LOG_DB', 'VAS_TOEMAIL_DB');
Then to recompile invalid packages:
EXEC UTL_RECOMP.recomp_serial('CRM_DB');
EXEC UTL_RECOMP.recomp_serial('PROV_DB');
EXEC UTL_RECOMP.recomp_serial('RAF_CONS_DB');
EXEC UTL_RECOMP.recomp_serial('RATING_DB');
EXEC UTL_RECOMP.recomp_serial('RESOURCE_DB');
EXEC UTL_RECOMP.recomp_serial('SHARED_DB');
Or to perform the same task in parallel using the specified number of threads
EXEC UTL_RECOMP.recomp_parallel(4, 'PROV_DB');
or at database level:
EXEC UTL_RECOMP.recomp_parallel(4);
See http://www.oracle-base.com/articles/misc/recompiling-invalid-schema-objects.php for more details