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 |