DBMS_FILE_TRANSFER   an ORACLE BUILD-IN package      june 22, 2007

this is a nice package that you can use to transfer file between directories within a local server or with directories within a local server and remote server.

Since, this package is designed to transfer Oracle database file (making up tablespace) between different instances of databases, it assumes that Oracle database is running on the location where the files are manipulated (on both local and remote if you are doing across server transfer). Or at least the listener must be running (i would assume)

dbms_file_transfer:

There are three procedures within the dbms_file_transfer package:

COPY_FILE: This is useful for copying files locally on the database server.

GET_FILE: This is useful when a file on a remote database is to be transferred to a local file system through the local database connection.

PUT_FILE: Reads a local file and contacts a remote database to create a copy of the file in the remote file system



CREATE DIRECTORY DEST_DIR AS '/u01/src';
CREATE DIRECTORY SOURCE_DIR AS '/home/oracle/infra/oradata';

As user SYS, create the necessary grants.

GRANT EXECUTE ON DBMS_FILE_TRANSFER to DBA
GRANT READ ON DIRECTORY SOURCE_DIR to DBA
GRANT WRITE ON DIRECTORY DEST_DIR to DBA

.As the DBA user, execute the following command.
 

BEGIN
dbms_file_transfer.copy_file( source_directory_object => 'SOURCE_DIR',
source_file_name => 'users1.dbf',

destination_directory_object => 'DEST_DIR',
destination_file_name => 'users1.dbf');
END;
/
 

The following example is to GET a file from a remote server

BEGIN
dbms_file_transfer.copy_file( source_directory_object => 'SOURCE_DIR',
source_file_name => 'users1.dbf',

source_database =>'DB1_LINK',
destination_directory_object => 'DEST_DIR',
destination_file_name => 'users1.dbf');
END;
/
 

Note:  the source_database is identified as a database link that can be created using the following syntax

CREATE DATABASE LINK db1_link CONNECT TO user1 IDENTIFIED BY userpass USING 'linux'; -- linux is the connect string name in tnanames.ora file