Below is the script to compile all the INVALID objects and script to compile specific objects in oracle database.
************************************************************************************
SET SERVEROUTPUT ON SIZE 1000000
BEGIN
FOR cur_rec IN (SELECT owner,
object_name,
object_type,
FROM dba_objects
WHERE object_type IN (‘PACKAGE’, ‘PACKAGE BODY’,’SYNONYM’,’TRIGGER’,’VIEW’)
AND status != ‘VALID’
ORDER BY 3)
LOOP
BEGIN
IF cur_rec.object_type = ‘PACKAGE BODY’ THEN
EXECUTE IMMEDIATE ‘ALTER PACKAGE “‘ || cur_rec.owner ||
‘”.”‘ || cur_rec.object_name || ‘” COMPILE BODY’;
ElSE
EXECUTE IMMEDIATE ‘ALTER ‘ || cur_rec.object_type ||
‘ “‘ || cur_rec.owner || ‘”.”‘ || cur_rec.object_name || ‘” COMPILE’;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(cur_rec.object_type || ‘ : ‘ || cur_rec.owner ||
‘ : ‘ || cur_rec.object_name);
END;
END LOOP;
END;
/
select ‘alter package ‘||owner||’.’||object_name||’ compile;’ from dba_objects where object_type=’PACKAGE’ and status=’INVALID’;
select ‘alter package ‘||owner||’.’||object_name||’ compile body;’ from dba_objects where object_type=’PACKAGE BODY’ and status=’INVALID’;
select ‘alter synonym ‘||owner||’.’||object_name||’ compile;’ from dba_objects where object_type=’SYNONYM’ and status=’INVALID’;
select ‘alter trigger ‘||owner||’.’||object_name||’ compile;’ from dba_objects where object_type=’TRIGGER’ and status=’INVALID’;
select ‘alter view ‘||owner||’.’||object_name||’ compile;’ from dba_objects where object_type=’VIEW’ and status=’INVALID’;
************************************************************************************
