Sunday, March 14, 2010

How to compile all the invalid objects in Oracle Database?

Below is the script to generate dynamic script to compile all the invalid objects in Oracle Database.

set echo off
set feed off
set sqlp ''
set head off
set pages 0
spool spool_output.sql
select 'ALTER '||
DECODE(object_type, 'VIEW', 'VIEW', 'PACKAGE') || ' '||
LOWER(object_name)|| ' COMPILE '||
DECODE(object_type, 'PACKAGE', 'SPECIFICATION', 'PACKAGE BODY', 'BODY', '')|| ' ; ' from user_objects
where status = 'INVALID'
order by DECODE(object_type, 'VIEW', 1, 'PACKAGE', 2, 'PACKAGE BODY', 3), object_name ;
spool off
set echo on
set feed on
set sqlp 'SQL>'
set head on

select 'alter '||object_type||' '||object_name||' compile;' from user_objects where status='INVALID' order by object_type,object_name;

No comments: