DBMS_FILE_TRANSFER Package in Oracle Database 10g

 

from:http://www.oracle-base.com/articles/10g/FileTransfer10g.php

 

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

• Common Usage Notes
• COPY_FILE
• GET_FILE
• PUT_FILE

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;

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

 

 

 

---

 

DBMS_FILE_TRANSFER   an ORACLE BUILD-IN package      june 22, 2007

 

 

from:http://www.e-ammar.com/dbms_file_transfer.htm

 

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