Useful shortcuts for vi editor

Tuesday 11 October 2016

How to recompile invalid objects and compile all database objects?

Generate and Run SQL for recompiling invalid objects
serial option:
SQL> 
EXEC UTL_RECOMP.recomp_serial();
SQL> 
select distinct 'EXEC UTL_RECOMP.recomp_serial(' || '''' || owner || ''');' AS SQL from dba_objects where status = 'INVALID';

parallel option (4 parallel threads):
SQL> 
EXEC UTL_RECOMP.recomp_parallel(4);
SQL> 
select distinct 'EXEC UTL_RECOMP.recomp_parallel( 4, ' || '''' || owner || ''');' AS SQL from dba_objects where status = 'INVALID';

or recompile with manual approach
SQL> 
select 'ALTER ' || (CASE WHEN object_type = 'PACKAGE BODY' THEN 'PACKAGE' ELSE object_type END) || ' ' || owner || '.' || object_name || ' COMPILE' || (CASE WHEN object_type = 'PACKAGE BODY' THEN ' BODY;' ELSE ';' END) AS SQL from dba_objects where status = 'INVALID' and object_type <> 'SYNONYM' order by owner;

Generate and Run SQL for compile all objects
SQL> 
select distinct 'EXEC DBMS_UTILITY.compile_schema(schema =>' || '''' || owner || ''')' AS SQL from dba_objects where status = 'INVALID';

Check invalid objects again
SQL> 
select * from dba_objects where status = 'INVALID' order by owner;

Count invalid objects group by object type
SQL>
select object_type,count(*) from dba_objects where status = 'INVALID' group by object_type;