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;