Orace 12c Export local database and import to remote database

Export local database and import to remote database

###Export local database to dmp file
Execute on sql developer client with system role

alter session set "_oracle_script" = true;

drop user user_test cascade;

drop tablespace BIGTBS_03 including contents and datafiles;

CREATE BIGFILE TABLESPACE BIGTBS_03  DATAFILE 'C:/tableSpaces/BIGTBS_F3.DAT'  SIZE 500M AUTOEXTEND ON NEXT 100M;

CREATE USER user_test IDENTIFIED BY user_pass DEFAULT TABLESPACE bigtbs_03 QUOTA UNLIMITED ON bigtbs_03 TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON system;

grant all privileges to user_test;

expdp user_test/user_pass@xe directory=DATA_PUMP_DIR dumpfile=EXPDP_local.dmp logfile=EXPDP_local.log tables=IMAGES,GENERIC

###Copy local dmp file to remote database via dblink

drop public database link to_rds;

create public database link to_rds connect to user_test_rds identified by user_pass_rds
	using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=remote.server.ip)(PORT=1521))(CONNECT_DATA=(SID=xe)))';

DBMS_FILE_TRANSFER.PUT_FILE(
    source_directory_object       => 'DATA_PUMP_DIR',
    source_file_name              => 'EXPDP_local.DMP',
    destination_directory_object  => 'DATA_PUMP_DIR',
    destination_file_name         => 'EXPDP_rds_rds.DMP', 
    destination_database          => 'to_rds'
);

###Import dmp file in remote database

impdp user_test_rds/user_pass_rds@rds_servicename logfile=EXPDP_rds_rds.DMP.log dumpfile=EXPDP_rds_rds.DMP directory=DATA_PUMP_DIR TABLE_EXISTS_ACTION=APPEND tables=IMAGES,GENERIC
   
    1. only import data: CONTENT=DATA_ONLY 
    2. table exists then append data records: TABLE_EXISTS_ACTION=APPEND 
    3. not import index,CONSTRAINT: EXCLUDE=INDEX,CONSTRAINT 

###SQL file implement function copy local dmp file to remote database via dblink
Execute on CMD window: win + R + cmd(windows)

sqlplus -S local_user_sys/local_pass_system@xe as sysdba @C:\Sql\oracle_db_copy_to_rds.sql EXPDP_rds_rds.DMP user_pass_rds

Oracle_db_copy_to_rds.sql

File: C:\Sql\oracle_db_copy_to_rds.sql

    SET SERVEROUTPUT ON SIZE 999999;
    WHENEVER SQLERROR EXIT FAILURE ROLLBACK;

    DECLARE
    script_name   CONSTANT VARCHAR2 (50) := 'oracle_db_copy_to_rds.sql ';
    BEGIN
        DBMS_OUTPUT.new_line ();
        DBMS_OUTPUT.put_line ('File ' || script_name || 'started at ' || TO_CHAR (CURRENT_DATE, 'MON-DD-YYYY HH24:MI:SS'));
        
        EXECUTE IMMEDIATE 'create public database link to_rds connect to user_test_rds identified by &2
        using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=remote.server.ip)(PORT=1521))(CONNECT_DATA=(SID=xe)))';

        DBMS_FILE_TRANSFER.PUT_FILE(
            source_directory_object       => 'DATA_PUMP_DIR',
            source_file_name              => 'EXPDP_local.DMP',
            destination_directory_object  => 'DATA_PUMP_DIR',
            destination_file_name         => '&1', 
            destination_database          => 'to_rds'
        );
        
    DBMS_OUTPUT.put_line ('File ' || script_name || ' ended at ' || TO_CHAR (CURRENT_DATE, 'MON-DD-YYYY HH24:MI:SS'));
    END;
    /
    commit;
    exit;

###Directory Operation

select * from dba_directories;
create directory or replace DATA_PUMP_DIR as 'c:\dump';
grant read,write on directory DATA_PUMP_DIR to user_test;