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;
/

DECLARE
  CURSOR dynamo_tables IS select * from all_all_tables where owner = 'DYNAMO';
BEGIN
  FOR next_row IN dynamo_tables
  LOOP
    execute immediate 'CREATE or REPLACE SYNONYM '||next_row.table_name||' for DYNAMO.'||next_row.table_name;
  END LOOP;
END;
/

CREATE SYNONYMS

PL/SQL