How to Transfer file from AWS RDS for Oracle to Other Oracle Server

I have shown here how to transfer file from Oracle RDS database to Non RDS Oracle server to access the file. In that example we will transfer file from Source (Oracle RDS) to Target (Non RDS Oracle server).

Steps:

Target (Non RDS Oracle Server):

1. Create users in target (If you already don’t have users to connect from source)

2. Create directory if don’t exist (Here I have used existing directory 'DATA_PUMP_DIR')

3. Grant the user write privilege on the directory for transferring file from source

Source (Oracle RDS):

4. Create database link to target

5. Transfer the file using UTL_FILE package

Target (Non RDS Oracle Server):

# sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Feb 19 15:15:53 2019

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:

Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

SQL> create user transferfile identified by tr;

User created.

SQL> grant create session,resource to transferfile;

Grant succeeded.

SQL> select DIRECTORY_PATH from all_directories where DIRECTORY_NAME='DATA_PUMP_DIR';

DIRECTORY_PATH

--------------------------------------------------------------------------------

/oracle/TARGET/oradata/data/data_pump_dir

SQL> grant read,write on directory DATA_PUMP_DIR to transferfile;

Grant succeeded.

Source (Oracle RDS):

select * from table(rdsadmin.rds_file_util.listdir('DATA_PUMP_DIR')) where filename='Export_full.csv';

create database link to_transfer

connect to transferfile identified by tr

using '(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = XXX.XX.XXXX.XXX)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = TARGET.US.oracle.com)))';

declare

lfh utl_file.file_type;

rfh utl_file.file_type@to_transfer;

ldata raw(32767);

begin

-- open file handles

lfh := utl_file.fopen(location=>'DATA_PUMP_DIR', filename=>'Export_full.csv', open_mode=>'rb');

rfh := utl_file.fopen@to_transfer(location=>'DATA_PUMP_DIR', filename=>'Export_full.csv', open_mode=>'wb');

-- iterate local file and write it to remote

begin

loop

begin

utl_file.get_raw(lfh, ldata, 32767);

utl_file.put_raw@to_transfer(rfh, ldata, true);

exception

when no_data_found then

exit;

end;

end loop;

end;

-- close file handles

utl_file.fclose(lfh);

utl_file.fclose@to_transfer(rfh);

exception

-- exception handling

when others then

utl_file.fclose(lfh);

utl_file.fclose@to_transfer(rfh);

raise;

end;

/

Now check the file in Target:

# pwd

/oracle/TARGET/oradata/data/data_pump_dir

# ls -lrt

-rw-rw-r--. 1 target target 3588901688 Feb 19 16:58 Export_full.csv