Tuesday, January 21, 2014

How to use the DBMS_FILE_TRANSFER.PUT_FILE procedure

BEGIN
  SYS.DBMS_FILE_TRANSFER.PUT_FILE(
   source_directory_object      => 'DPUMP',
   source_file_name             => 'myfile.txt',
   destination_directory_object => 'REMOTE_DPDUMP',
   destination_file_name        => 'myfile.txt',
   destination_database         => 'REFRESH.MYDOMAIN.COM');
END;
/

Used in a script:

export DMP_NAME=`echo $1 | tr '[a-z]' '[A-Z]'`
export DPDIR=`echo $4 | tr '[a-z]' '[A-Z]'`

#####################################################
# Transfer to remote server using DBMS_FILE_TRANSFER
#####################################################

cat << EoF > ${DBA_ADMIN}/sql/copy_file.sql
set trimspool on
spool copy_file.log
Prompt Transferring Dumpfiles;
define file_name=&1


BEGIN
  SYS.DBMS_FILE_TRANSFER.PUT_FILE(
   source_directory_object      => 'DPUMP',
   source_file_name             => '&file_name',
   destination_directory_object => '${DPDIR}',
   destination_file_name        => '&file_name',
   destination_database         => 'REFRESH.MYDOMAIN.COM');
END;
/
exit
EoF

for dmpfile in $(ls /oracle/datapump/${DMP_NAME}_*.dmp); do
 file_name=`echo $dmpfile | cut -d / -f 7`

sqlplus -s / as sysdba @${DBA_ADMIN}/sql/copy_file.sql ${file_name} > ${BATCHDIR}/file_name.log 2> ${BATCHDIR}/file_name.err &
done
wait

rm -f  ${DBA_ADMIN}/sql/copy_file.sql

No comments:

Post a Comment