CREATE SYNONYMS

DECLARE
  src_schema VARCHAR2(256) := 'EMPIK_DYNAMO_01';
  target_schema VARCHAR2(256) := 'EMPIK_PORTAL_BETA_1';
  CURSOR src_objects IS
    SELECT table_name AS object_name FROM all_all_tables WHERE owner = src_schema
    UNION
    SELECT sequence_name AS object_name FROM all_sequences WHERE sequence_owner = src_schema;
BEGIN
  FOR next_row IN src_objects LOOP
    BEGIN
      EXECUTE IMMEDIATE 'CREATE or REPLACE SYNONYM ' || target_schema || '.' || next_row.object_name || ' for ' || src_schema || '.' || next_row.object_name;
    EXCEPTION WHEN OTHERS THEN
      DBMS_OUTPUT.put_line('ERROR WHILE CREATING SYNONYM FOR: ' || next_row.object_name);
      DBMS_OUTPUT.put_line(SQLERRM);
    END;
  END LOOP;
END;
/