You can copy any type of file to and from a local file system. However, you can copy only database files (such as datafiles, tempfiles, controlfiles, and so on) to and from an ASM disk group.

The destination file is not closed until the procedure completes successfully.

Syntax

DBMS_FILE_TRANSFER.COPY_FILE(
   source_directory_object       IN  VARCHAR2,
   source_file_name              IN  VARCHAR2,
   destination_directory_object  IN  VARCHAR2,
   destination_file_name         IN  VARCHAR2);

Parameters

Usage Notes

To run this procedure successfully, the current user must have the following privileges:

• READ privilege on the directory object specified in the source_directory_object parameter

• WRITE privilege on directory object specified in the destination_directory_object parameter

This procedure converts directory object parameters to uppercase unless they are surrounded by double quotation marks, but this procedure does not convert file names to uppercase.

Also, the copied file must meet the following requirements:

• The size of the copied file must be a multiple of 512 bytes.

• The size of the copied file must be less than or equal to two terabytes.

The source_file_name parameter must specify a file that is in the directory specified by the source_directory_object parameter before running the procedure, and the destination_file_name parameter must specify the new name of the file in the new location specified in the destination_directory_object parameter. Relative paths and symbolic links are not allowed in the directory objects for the source_directory_object and destination_directory_object parameters.

Transferring the file is not transactional. To monitor the progress of a long file copy, query the V$SESSION_LONGOPS dynamic performance view.

Examples

SQL> create directory DGROUP as '+diskgroup1/dbs/backup';
 
Directory created.
 
SQL>  BEGIN
   2    DBMS_FILE_TRANSFER.COPY_FILE('SOURCEDIR','t_xdbtmp.f', 'DGROUP', 
                                   't_xdbtmp.f');
   3  END;
   4  /
 
PL/SQL procedure successfully completed.
 
SQL> EXIT
$ASMCMD
ASMCMD> ls
DISKGROUP1/
ASMCMD> cd diskgroup1/dbs/backup
ASMCMD> ls
t_xdbtmp.f => +DISKGROUP1/ORCL/TEMPFILE/COPY_FILE.267.546546525

Syntax

DBMS_FILE_TRANSFER.GET_FILE
   source_directory_object      IN  VARCHAR2,    
   source_file_name             IN  VARCHAR2,  
   source_database              IN  VARCHAR2,  
   destination_directory_object IN  VARCHAR2,
   destination_file_name        IN  VARCHAR2);

Parameters

Usage Notes

To run this procedure successfully, the following users must have the following privileges:

• The connected user at the source database must have read privilege on the directory object specified in the source_directory_object parameter.

• The current user at the local database must have write privilege on the directory object specified in the destination_directory_object parameter.

This procedure converts directory object parameters to uppercase unless they are surrounded by double quotation marks, but this procedure does not convert file names to uppercase.

Also, the copied file must meet the following requirements:

• The size of the copied file must be a multiple of 512 bytes.

• The size of the copied file must be less than or equal to two terabytes.

Transferring the file is not transactional. To monitor the progress of a long file transfer, query the V$SESSION_LONGOPS dynamic performance view.

Examples

CREATE OR REPLACE DIRECTORY df AS '+datafile' ;
GRANT WRITE ON DIRECTORY df TO "user";
CREATE DIRECTORY DSK_FILES AS ''^t_work^'';
GRANT WRITE ON DIRECTORY dsk_files TO "user";
 
-- asumes that dbs2 link has been created and we are connected to the instance. 
-- dbs2 could be a loopback or point to another instance.
 
BEGIN
-- asm file to an os file
-- get an asm file from dbs1.asm/a1 to dbs2.^t_work^/oa5.dat
  DBMS_FILE_TRANSFER.GET_FILE ( 'df' , 'a1' , 'dbs1', 'dsk_files' , 'oa5.dat' );
 
-- os file to an os file
-- get an os file from dbs1.^t_work^/a2.dat to dbs2.^t_work^/a2back.dat
  DBMS_FILE_TRANSFER.GET_FILE ( 'dsk_files' , 'a2.dat' , 'dbs1', 'dsk_files' , 'a2back.dat' );
 
END ;
/

The file that is copied is the source file, and the new file that results from the copy is the destination file. The destination file is not closed until the procedure completes successfully.

Syntax

DBMS_FILE_TRANSFER.PUT_FILE(
   source_directory_object       IN  VARCHAR2,   
   source_file_name              IN  VARCHAR2,
   destination_directory_object  IN  VARCHAR2,
   destination_file_name         IN  VARCHAR2,  
   destination_database          IN  VARCHAR2);

Parameters

Usage Notes

To run this procedure successfully, the following users must have the following privileges:

• The current user at the local database must have read privilege on the directory object specified in the source_directory_object parameter.

• The connected user at the destination database must have write privilege to the directory object specified in the destination_directory_object parameter.

This procedure converts directory object parameters to uppercase unless they are surrounded by double quotation marks, but this procedure does not convert file names to uppercase.

Also, the copied file must meet the following requirements:

• The size of the copied file must be a multiple of 512 bytes.

• The size of the copied file must be less than or equal to two terabytes.

Transferring the file is not transactional. To monitor the progress of a long file transfer, query the V$SESSION_LONGOPS dynamic performance view.

Examples

CREATE OR REPLACE DIRECTORY df AS '+datafile' ;
GRANT WRITE ON DIRECTORY df TO "user";
CREATE OR REPLACE DIRECTORY ft1 AS '+datafile/ft1' ;
GRANT READ,WRITE ON DIRECTORY ft1 TO "user";
CREATE OR REPLACE DIRECTORY ft1_1 AS '+datafile/ft1/ft1_1' ;
 
CONNECT user;
Enter password: password
 
-- - put a1.dat to a4.dat (using dbs2 dblink)
-- - level 2 sub dir to parent dir
-- - user has read privs on ft1_1 at dbs1 and write on df in dbs2
BEGIN
 DBMS_FILE_TRANSFER.PUT_FILE ( 'ft1_1' , 'a2.dat' , 'df' , 'a4.dat' ,
                               'dbs2' ) ;
END ;