DECLARE
  v_execute           BOOLEAN         := FALSE;
  v_host              VARCHAR2 (50)   := '&&DB_HOST';
  v_port              VARCHAR2 (50)   := '&&DB_PORT';
  v_sid               VARCHAR2 (50)   := '&&DB_SID';
  v_user_name         VARCHAR2 (50)   := '&&DB_USER_NAME';
  v_password          VARCHAR2 (50)   := '&&DB_PASSWORD';
  v_connection_string VARCHAR2 (1024) :=
  '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ' || v_host || ')(PORT = ' ||
  v_port || ')))(CONNECT_DATA =(SID = ' || v_sid || ')))';
  v_db_link_name VARCHAR2 (1024) := 'TMP_DB_LINK_' || TO_CHAR (SYSDATE, 'YYYYMMDDHH24MISS');
  v_statement    VARCHAR2 (8192);
  v_column_list  VARCHAR2 (8192);
  CURSOR c_table
  IS
    SELECT
      table_name
    FROM
      user_tables
    WHERE
      INSTR (table_name, '$') <= 0
    ORDER BY
      table_name;
  CURSOR c_column (p_table_name VARCHAR2)
  IS
    SELECT
      column_name
    FROM
      user_tab_cols
    WHERE
      table_name                  = p_table_name
    AND INSTR (column_name, '$') <= 0;
BEGIN
  v_statement := 'CREATE DATABASE LINK ' || v_db_link_name || ' CONNECT TO ' || v_user_name ||
  ' IDENTIFIED BY ' || v_password || ' USING ''' || v_connection_string || '''';
  DBMS_OUTPUT.put_line( TO_CHAR (SYSDATE, '-- DD/MM HH24:MI:SS') || ': Start executing statement:'
  || CHR (10) || v_statement || ';');
  IF v_execute THEN
    EXECUTE IMMEDIATE v_statement;
  END IF;
  DBMS_OUTPUT.put_line ( TO_CHAR (SYSDATE, '-- DD/MM HH24:MI:SS') || ': Completed');
  FOR r_table IN c_table
  LOOP
    v_column_list := '';
    FOR r_column IN c_column (r_table.table_name)
    LOOP
      v_column_list := v_column_list || ',' || r_column.column_name;
    END LOOP;
    v_column_list := SUBSTR (v_column_list, 2);
    BEGIN
      v_statement := 'insert into ' || r_table.table_name || '(' || v_column_list || ')' || chr(10)
      || 'select ' || v_column_list || ' from ' || r_table.table_name || '@' || v_db_link_name;
      DBMS_OUTPUT.put_line( TO_CHAR (SYSDATE, '-- DD/MM HH24:MI:SS') ||
      ': Start executing statement:' || CHR (10) || v_statement || ';');
      IF v_execute THEN
        EXECUTE IMMEDIATE v_statement;
      END IF;
      DBMS_OUTPUT.put_line ( TO_CHAR (SYSDATE, '-- DD/MM HH24:MI:SS') || ': Completed');
    EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.put_line(TO_CHAR (SYSDATE, '-- DD/MM HH24:MI:SS') || ': Error occurred: ' ||
      SQLERRM);
    END;
  END LOOP;
  v_statement := 'DROP DATABASE LINK ' || v_db_link_name;
  DBMS_OUTPUT.put_line( TO_CHAR (SYSDATE, '-- DD/MM HH24:MI:SS') || ': Start executing statement:'
  || CHR (10) || v_statement || ';');
  IF v_execute THEN
    EXECUTE IMMEDIATE v_statement;
  END IF;
  DBMS_OUTPUT.put_line ( TO_CHAR (SYSDATE, '-- DD/MM HH24:MI:SS') || ': Completed');
END;