--sqlplus sys/admin@127.0.0.1/gcoop as sysdba

-- 2.1 สร้าง dblink ไปยัง user admin ของ Amazon oracle ในที่นี้คือ taacoop/Airasia123 
drop  database link to_rds ;
create database link to_rds connect to taacoop identified by  Airasia123 using 'taacoop.cuh6uuk8ensz.ap-southeast-1.rds.amazonaws.com:1521/taacoop';
--2.2 สร้าง script mapp path 
DECLARE
hdnl NUMBER;
BEGIN
hdnl := DBMS_DATAPUMP.OPEN( operation => 'EXPORT', job_mode => 'SCHEMA', job_name=>null);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => 'ISCOTAA.DMP', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_dump_file);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => 'ISCOTAA.LOG', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_LOG_file);
DBMS_DATAPUMP.METADATA_FILTER(hdnl,'SCHEMA_EXPR','IN (''ISCOTAA'')');
DBMS_DATAPUMP.START_JOB(hdnl);
END;
/    
-- 2.3 ตรวจสอบ PATH ISCOTAA.DMP ก่อน upload 
select directory_path from all_directories where upper(directory_name) = 'DATA_PUMP_DIR';

-- 2.4 ทำการ Upload ไปที่ Amazon oracle
BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(
source_directory_object   	=> 'DATA_PUMP_DIR',
source_file_name          	=> 'ISCOTAA.DMP',
destination_directory_object  => 'DATA_PUMP_DIR',
destination_file_name     	=> 'ISCOTAA.DMP',
destination_database      	=> 'to_rds'
);
END;
/

--Quit;