DBMS_FILE_TRANSFER Package in Oracle Database 10g

Oracle 10g has introduced the DBMS_FILE_TRANSFER package which provides an API for copying binary files between database servers

Common Usage NotesAll of the the currently supported procedures have some common usage notes listed below:

The user must have read privilege on the source directory object and write privilege on the destination directory object.

The procedure converts directory object names to uppercase unless they are surrounded by double quotes.

Files to be copied must be multiples of 512 bytes in size.

Files to be copied must be equal to or less than 2 terabytes in size.

File transfers are not transactional.

Files are copied as binary, so no character conversions are performed.

File copies can be monitored using the V$SESSION_LONGOPS view.

COPY_FILE

The COPY_FILE procedure allows you to copy binary files from one location to another on the same server.

-- Create the source and destination directory objects.
CREATE OR REPLACE DIRECTORY db_files_dir1 AS '/u01/oradata/DB10G/';
CREATE OR REPLACE DIRECTORY db_files_dir2 AS '/u02/oradata/DB10G/';
-- Switch a tablespace into read only mode so we can
-- use it for a test file transfer.
ALTER TABLESPACE users READ ONLY;
-- Copy the file.
BEGIN
DBMS_FILE_TRANSFER.COPY_FILE(
source_directory_object => 'DB_FILES_DIR1',
source_file_name => 'USERS01.DBF',
destination_directory_object => 'DB_FILES_DIR2',
destination_file_name => 'USERS01.DBF');
END;
/
-- Switch the tablespace back to read write mode.
ALTER TABLESPACE users READ WRITE;

GET_FILE

The GET_FILE procedure allows you to copy binary files from a remote server to the local server.

-- Login to the remote server.
CONN system/password@remote

-- Create the source directory object and switch mode of a tablespace.
CREATE OR REPLACE DIRECTORY db_files_dir1 AS '/u01/oradata/DB10G/';
ALTER TABLESPACE users READ ONLY;

-- Login to the local server.
CONN system/password@local

-- Create the destination directory object and a database link.
CREATE OR REPLACE DIRECTORY db_files_dir2 AS '/u02/oradata/DB10G/';
CREATE DATABASE LINK remote CONNECT TO system IDENTIFIED BY password USING 'REMOTE';

-- Get the file.

BEGIN
DBMS_FILE_TRANSFER.GET_FILE(
source_directory_object => 'DB_FILES_DIR1',
source_file_name => 'USERS01.DBF',
source_database => 'REMOTE',
destination_directory_object => 'DB_FILES_DIR2',
destination_file_name => 'USERS01.DBF');
END;
/

-- Login to the remote server.
CONN system/password@remote

-- Switch the tablespace back to read write mode.
ALTER TABLESPACE users READ WRITE;

PUT_FILE

The PUT_FILE procedure allows you to copy binary files from the local server to a remote server.

-- Login to the remote server.
CONN system/password@remote

-- Create the destination directory object.
CREATE OR REPLACE DIRECTORY db_files_dir2 AS '/u02/oradata/DB10G/';

-- Login to the local server.
CONN system/password@local

-- Create the source directory object, database link and switch mode of a tablespace.
CREATE OR REPLACE DIRECTORY db_files_dir1 AS '/u01/oradata/DB10G/';
CREATE DATABASE LINK remote CONNECT TO system IDENTIFIED BY password USING 'REMOTE';
ALTER TABLESPACE users READ ONLY;

-- Put the file.
BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(
source_directory_object => 'DB_FILES_DIR1',
source_file_name => 'USERS01.DBF',
destination_directory_object => 'DB_FILES_DIR2',
destination_file_name => 'USERS01.DBF',
destination_database => 'REMOTE');
END;
/

-- Switch the tablespace back to read write mode.
ALTER TABLESPACE users READ WRITE;

Checking the destination directory on the remote server will reveal that the file has been copied successfully.