Useful DBA SQLs. Compile and Recompile Objects.

=======================
Compile Invalid Objects.
=======================

=======================
Packages
=======================

ALTER PACKAGE XXX COMPILE SPECIFICATION;

ALTER PACKAGE XXX COMPILE BODY;

To see compilation errors:
SELECT * FROM USER_ERRORS;


SELECT object_type, 'ALTER '||
       DECODE(object_type,'PACKAGE BODY','PACKAGE',object_type)||
       ' ' ||owner||'.'||object_name||' COMPILE '||
       decode(object_type,'PACKAGE BODY','BODY','')||';' "ALTER ... COMPILE"
FROM DBA_OBJECTS
WHERE status='INVALID'
  AND object_type NOT IN('JAVA CLASS','JAVA SOURCE')
  AND (object_type != 'SYNONYM' AND OWNER!='PUBLIC')
  AND owner <> 'SYS'

UNION ALL

SELECT object_type, 'ALTER TRIGGER '||' "'||owner||'.'||objest_name||'" COMPILE; '
FROM  DBA_OBJECTS 
WHERE status='INVALID' 
  AND object_type in('TRIGGER')

UNION ALL

SELECT object_type, 'ALTER '||object_type||' "'||owner||'.'||object_name||'" COMPILE; '
FROM  DBA_OBJECTS 
WHERE status='INVALID' 
  AND object_type in('JAVA CLASS','JAVA SOURCE')

UNION ALL

SELECT object_type, 'ALTER ' || OWNER || ' ' || OBJECT_TYPE ||' '||OBJECT_NAME || ' COMPILE;'
FROM DBA_OBJECTS
WHERE STATUS='INVALID'
  AND OBJECT_TYPE='SYNONYM'
  AND owner = 'PUBLIC'
--order by object_type, name
ORDER BY 1,2;



=======================
Synonyms
=======================

ALTER SYNONYM MY_SYNONYM COMPILE;