Q：对 ALL_SYNONYMS 中查询出的、非系统用户的同义词，批量执行 CREATE OR REPLACE SYNONYM，实现重建效果

使用 PL/SQL 块自动重建

以下是一个安全、带异常捕获、可审计的 PL/SQL 脚本，用于批量重建你筛选出的同义词

BEGIN
    FOR rec IN (
        SELECT owner, synonym_name, 
               'CREATE OR REPLACE SYNONYM ' || owner || '.' || synonym_name || 
               ' FOR ' || table_owner || '.' || table_name ||
               CASE WHEN db_link IS NOT NULL THEN '@' || db_link ELSE '' END AS stmt
        FROM all_synonyms
        WHERE table_owner NOT IN ('SYS', 'SYSTEM', 'XDB', 'WMSYS', 'CTXSYS', 'MDSYS', 'OLAPSYS')
          AND owner != 'PUBLIC'  -- 明确排除 PUBLIC
        ORDER BY owner, synonym_name
    ) LOOP
        BEGIN
            EXECUTE IMMEDIATE rec.stmt;
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('❌ 失败: ' || rec.owner || '.' || rec.synonym_name || 
                                   ' -> ' || SQLERRM);
        END;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('✅ 所有同义词重建完成。');
END;
/

 

生成预览脚本（不执行）

SELECT 'CREATE OR REPLACE SYNONYM ' || owner || '.' || synonym_name || 
       ' FOR ' || table_owner || '.' || table_name ||
       CASE WHEN db_link IS NOT NULL THEN '@' || db_link ELSE '' END || ';' AS script
FROM all_synonyms
WHERE table_owner NOT IN ('SYS', 'SYSTEM', 'XDB', 'WMSYS', 'CTXSYS', 'MDSYS', 'OLAPSYS')
  AND owner NOT IN ('PUBLIC')
ORDER BY owner, synonym_name;