Compile All Objects

SELECT 
  'alter ' ||
  DECODE(OBJECT_TYPE, 'PACKAGE BODY', 'PACKAGE', OBJECT_TYPE) || ' ' ||
  OWNER || '.' || OBJECT_NAME || ' compile ' ||
  DECODE(OBJECT_TYPE, 'PACKAGE BODY', 'BODY;', ';')
FROM 
  DBA_OBJECTS
WHERE 
  OBJECT_TYPE NOT IN ('TABLE', 'INDEX', 'SEQUENCE')
  AND OWNER IN ('SCHEMA')