PROMPT #
PROMPT # 'SINONIMOS PUBLICOS FALTANTES ON INCORRETOS NA xxx - ZZZ'
PROMPT # 'Obs. Exceto SYS, SYSTEM'
PROMPT #

COL "SINONIMOS IRREGULARES" FORMAT A80
SELECT /*+rule*/ 'CREATE PUBLIC SYNONYM ' ||O.OBJECT_NAME|| CHR(10) || 'FOR /* '||
       O.OBJECT_TYPE ||' */ ' ||O.OWNER|| '.' ||O.OBJECT_NAME|| ';' "SINONIMOS IRREGULARES"
FROM DBA_OBJECTS O
WHERE O.OWNER IN ( 'SCHEMA' )
AND O.OBJECT_TYPE NOT IN ( 'SYNONYM', 'INDEX', 'INDEX PARTITION', 'TABLE PARTITION', 'DATABASE LINK', 'TRIGGER', 'PACKAGE BODY', 'LOB' )
AND O.OBJECT_NAME NOT LIKE '%$RP'
AND O.OBJECT_NAME NOT LIKE '___NUM_DOC_MULTA_SEQ'
AND O.OBJECT_NAME NOT LIKE 'BIN$%'
AND NOT EXISTS ( SELECT 1
                 FROM DBA_SYNONYMS
                 WHERE TABLE_OWNER = O.OWNER
                 AND   TABLE_NAME = O.OBJECT_NAME
                 AND   TABLE_OWNER NOT IN ( 'SYS', 'SYSTEM' ) )
UNION ALL
SELECT /*+rule*/  'DROP PUBLIC SYNONYM ' || SYNONYM_NAME || ';' "SINONIMOS IRREGULARES"
FROM DBA_SYNONYMS S
WHERE OWNER = 'PUBLIC'
AND TABLE_OWNER NOT IN ( 'SYS', 'SYSTEM' )
AND NOT EXISTS ( SELECT 1
                 FROM DBA_OBJECTS
                 WHERE OBJECT_TYPE NOT IN ( 'SYNONYM', 'INDEX', 'DATABASE LINK', 'TRIGGER' )
                 AND OWNER NOT IN ( 'SYS', 'SYSTEM' )
                 AND OWNER = S.TABLE_OWNER
                 AND OBJECT_NAME = S.TABLE_NAME )
/