Synchronize and copy data from a remote Oracle Server to a local backup database

Synchronize and copy data from a remote Oracle Server to a local backup database

Create or replace PROCEDURE "SYNC_DATA_FROM_DBLINK_DB"
BEGIN
DECLARE
CURSOR c_TabNames IS
Select tname from tab;
V_TabName c_TabNames % ROWTYPE;
V_ SQL VARCHAR2 (500 );
V_rowcount NUMBER;
V_rc NUMBER: = 0;
V_tab NUMBER: = 0;
BEGIN
Dbms_Output.put_line (to_char (SYSDATE, 'yy-mm-dd hh24: mi: ss') | 'start to import the table from a remote TEST2 instance .');
FOR v_TabName in c_TabNames LOOP
V_ SQL: = 'delete' | v_TabName.TNAME;
Execute immediate v_ SQL;
V_ SQL: = 'insert' | v_tabname.TNAME | 'select * from' | v_tabname.TNAME | '@ test2 ';
Execute immediate v_ SQL;
COMMIT;
V_ SQL: = 'select COUNT (*) from' | v_tabname.TNAME;
Execute immediate v_ SQL INTO v_rowcount;
V_tab: = v_tab + 1;
V_rc: = v_rc + v_rowcount;
End loop;
Dbms_Output.put_line (to_char (SYSDATE, 'yy-mm-dd hh24: mi: ss') | 'import completed, total import table' | to_char (v_tab) |, total number of records '| to_char (v_rc) |' records. ');
END;
END SYNC_DATA_FROM_DBLINK_DB;