SET VERIFY OFF FEED OFF
DEFINE USU = "&1."

PROMPT
PROMPT EXECUTANDO DBMS_UTILITY.COMPILE_SCHEMA( UPPER( '&USU.' ) )
EXEC DBMS_UTILITY.COMPILE_SCHEMA( UPPER( '&USU.' ) )

COL STMT FORMAT A80
SET SERVEROUT ON
DECLARE
  CURSOR C IS
    SELECT 'ALTER ' ||
      DECODE( OWNER, 'PUBLIC', 'PUBLIC ', '' ) ||
      DECODE( OBJECT_TYPE, 'PACKAGE BODY', 'PACKAGE', OBJECT_TYPE )  || ' ' ||
      DECODE( OWNER, 'PUBLIC', '',  OWNER||'.' )  || OBJECT_NAME || ' COMPILE' ||
      DECODE( OBJECT_TYPE, 'PACKAGE BODY', ' BODY', '' )  STMT
    FROM DBA_OBJECTS
    WHERE STATUS <> 'VALID'
    AND OWNER = UPPER( '&USU.' )
    ORDER BY OBJECT_TYPE;
BEGIN
  DBMS_OUTPUT.ENABLE( 100000 );
  FOR R IN C LOOP
    BEGIN
      EXECUTE IMMEDIATE R.STMT;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE( R.STMT );
        DBMS_OUTPUT.PUT_LINE( SUBSTR(SQLERRM, 1, 254) || CHR(10) );
    END;
  END LOOP;
END;
/

SELECT OWNER, OBJECT_TYPE, OBJECT_NAME, STATUS
FROM DBA_OBJECTS
WHERE STATUS <> 'VALID'
AND OWNER = UPPER( '&USU.' );

SELECT COUNT(*) INVALIDOS
FROM DBA_OBJECTS
WHERE STATUS <> 'VALID'
AND OWNER = UPPER( '&USU.' );

SELECT OWNER, OBJECT_TYPE, COUNT(*) FROM
DBA_OBJECTS WHERE STATUS<>'VALID'
GROUP BY OWNER, OBJECT_TYPE
HAVING COUNT(*) >= 3
ORDER BY OWNER
/

SET PAGES 200
BREAK ON OWNER SKIP PAGE
SELECT owner, object_type, object_name, status
FROM dba_objects
WHERE owner in ( 'ADMSADP', 'EUL_OWNER', 'FOLHA', 'LINK', 'MANUTENCAO', 'PARTIDO', 'SIAC', 'SISFARM', 'SRH2' )
and status = 'INVALID'
order by owner, object_type, object_name

PROMPT
COL STMT CLEAR
SET VERIFY ON FEED 6
UNDEFINE 1 USU