Oracle dmp文件导入AWS RDS

http://docs.aws.amazon.com/zh_cn/AmazonRDS/latest/UserGuide/Oracle.Procedural.Importing.html

本地必须有个Oracle数据库。

先建立数据库到数据库的链路

create public database link to_rds connect to xxxxxx identified by xxxxxx
using ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx.xxx.xxx.xxx)(PORT=1521))(CONNECT_DATA=(SID=ORCL)))’;

然后复制本地dmp文件到远程（必须在相应的pump目录下）

BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(
source_directory_object => ‘DATA_PUMP_DIR’,
source_file_name => ‘xxx.DMP’,
destination_directory_object => ‘DATA_PUMP_DIR’,
destination_file_name => ‘xxx.DMP’,
destination_database => ‘to_rds’
);
END;

导入时使用

DECLARE
hdnl NUMBER;
BEGIN
hdnl := DBMS_DATAPUMP.open( operation => ‘IMPORT’, job_mode => ‘SCHEMA’, job_name=>null);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => ‘tab1.dmp’, directory => ‘DATA_PUMP_DIR’, filetype => dbms_datapump.ku$_file_type_dump_file);
DBMS_DATAPUMP.add_file( handle => hdnl, filename => ‘exp.log’, directory => ‘DATA_PUMP_DIR’, filetype => dbms_datapump.ku$_file_type_log_file);
DBMS_DATAPUMP.start_job(hdnl);
END;

最后查询并删除dump文件

select * from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR(‘DATA_PUMP_DIR’)) order by mtime;

DECLARE
BEGIN
utl_file.fremove(‘DATA_PUMP_DIR’,’SEA2_expdp_v_11.2.0.1.0.DMP’);
END;