set ver off;

SELECT 'ALTER '||OBJECT_TYPE||' '||OWNER||'.'||OBJECT_NAME||' COMPILE;'
FROM DBA_OBJECTS 
WHERE OWNER = UPPER('&1') AND STATUS = 'INVALID'
AND OBJECT_TYPE IN ('VIEW','PROCEDURE','FUNCTION','PACKAGE','TYPE','MATERIALIZED VIEW','TRIGGER');

SELECT 'ALTER PACKAGE '||OWNER||'.'||OBJECT_NAME||' COMPILE BODY;'
FROM DBA_OBJECTS 
WHERE OWNER = UPPER('&1') AND STATUS = 'INVALID'
AND OBJECT_TYPE IN ('PACKAGE BODY');

SELECT 'ALTER SYNONYM '||OWNER||'.'||SYNONYM_NAME||' COMPILE;'
FROM DBA_SYNONYMS
WHERE OWNER = UPPER('&1')
AND SYNONYM_NAME IN (SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OWNER = UPPER('&1') AND STATUS = 'INVALID' and OWNER <> UPPER('PUBLIC'));

SELECT 'ALTER PUBLIC SYNONYM '||SYNONYM_NAME||' COMPILE;'
FROM DBA_SYNONYMS
WHERE OWNER = UPPER('&1')
AND SYNONYM_NAME IN (SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OWNER = UPPER('&1') AND STATUS = 'INVALID' and OWNER = UPPER('PUBLIC'));

SELECT 'ALTER OPERATOR '||OWNER||'.'||OBJECT_NAME||' COMPILE;'
FROM DBA_OBJECTS 
WHERE OWNER = UPPER('&1') AND STATUS = 'INVALID'
AND OBJECT_TYPE IN ('OPERATOR');

SELECT 'ALTER INDEXTYPE '||OWNER||'.'||OBJECT_NAME||' COMPILE;'
FROM DBA_OBJECTS 
WHERE OWNER = UPPER('&1') AND STATUS = 'INVALID'
AND OBJECT_TYPE IN ('INDEXTYPE');

SELECT 'ALTER INDEX '||OWNER||'.'||OBJECT_NAME||' COMPILE;'
FROM DBA_OBJECTS 
WHERE OWNER = UPPER('&1') AND STATUS = 'INVALID'
AND OBJECT_TYPE IN ('INDEX');