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 Notes

All 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;

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

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;

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

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;