SET SERVEROUTPUT ON;

DECLARE
   v_owner   VARCHAR (100) := 'KFM';
BEGIN
   -- Удаление синонимов
   FOR r IN (SELECT *
               FROM dba_synonyms
              WHERE table_owner = v_owner)
   LOOP
    BEGIN
        EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM ' || r.synonym_name || ' FORCE';
      EXCEPTION
       WHEN OTHERS
       THEN
        DBMS_OUTPUT.put_line('Ошибка при удалении синонима ' || r.synonym_name || ': ' || SQLCODE ||' - ' || SQLERRM);
    END;
   END LOOP;

   -- Создание синонимов
   FOR r
      IN (SELECT *
            FROM ALL_OBJECTS
           WHERE     owner = v_owner
                 AND object_type IN
                        ('TABLE',
                         'PACKAGE',
                         'FUNCTION',
                         'PROCEDURE',
                         'VIEW',
                         'MATERIALIZED VIEW'))
   LOOP
     BEGIN
        EXECUTE IMMEDIATE
              'CREATE OR REPLACE PUBLIC SYNONYM ' || r.object_name || ' FOR ' || v_owner || '.' || r.object_name;
      EXCEPTION
         WHEN OTHERS
         THEN
          DBMS_OUTPUT.put_line('Ошибка при создании синонима для ' || v_owner || '.' || r.object_name || ': ' || SQLCODE ||' - ' || SQLERRM);
     END;
   END LOOP;
END;
/
