Oracle TIPS 14 – Sync two databases through db link
From the db that need to be synced (A), create a db link to the other db (B).
Create a procedure like the following to import evereything from the db linked (B) to the target db (A).
create or replace PROCEDURE import_prod_schema
as
JobHandle number;
js varchar2(9); -- COMPLETED or STOPPED
q varchar2(1) := chr(39);
BEGIN
-- open a new schema level import job using a default DB link
JobHandle := dbms_datapump.open ('IMPORT', 'SCHEMA', '[DBLINK_TO_B]');
-- restrict to the schema we want to copy
dbms_datapump.metadata_filter ( JobHandle, 'SCHEMA_LIST', q||'[SCHEMA NAME]'||q);
-- remap the importing schema name to the schema we want to create */
-- dbms_datapump.metadata_remap ( JobHandle, 'REMAP_SCHEMA', source_schema, destination_schema);
-- Set datapump parameters
dbms_datapump.set_parameter ( JobHandle, 'TABLE_EXISTS_ACTION', 'REPLACE' );
-- start the job
dbms_datapump.start_job( JobHandle);
-- wait for the job to finish
dbms_datapump.wait_for_job( JobHandle, js);
end;
