You do not necessarily have to use the operating system to copy a file within a database, or transfer a file between databases as you would do when using the transportable tablespace feature. You can use the
DBMS_FILE_TRANSFER package, or you can use Streams propagation. Using Streams is not discussed in this book, but an example of using the
DBMS_FILE_TRANSFER package is shown in "Copying a File on a Local File System".
DBMS_FILE_TRANSFER package can use a local file system or an Automatic Storage Management (ASM) disk group as the source or destination for a file transfer. Only Oracle database files (datafiles, tempfiles, controlfiles, and so on) can be involved in transfers to and from ASM.
Caution:Do not use the
DBMS_FILE_TRANSFERpackage to copy or transfer a file that is being modified by a database because doing so may result in an inconsistent file.
On UNIX systems, the owner of a file created by the
DBMS_FILE_TRANSFER package is the owner of the shadow process running the instance. Normally, this owner is
ORACLE. A file created using
DBMS_FILE_TRANSFER is always writable and readable by all processes in the database, but non privileged users who need to read or write such a file directly may need access from a system administrator.
This section contains the following topics:
Oracle Database PL/SQL Packages and Types Reference for a description of the
This section includes an example that uses the
COPY_FILE procedure in the
DBMS_FILE_TRANSFER package to copy a file on a local file system. The following example copies a binary file named
db1.dat from the
/usr/admin/source directory to the
/usr/admin/destination directory as
db1_copy.dat on a local file system:
In SQL*Plus, connect as an administrative user who can grant privileges and create directory objects using SQL.
Use the SQL command
CREATE DIRECTORY to create a directory object for the directory from which you want to copy the file. A directory object is similar to an alias for the directory. For example, to create a directory object called
SOURCE_DIR for the
/usr/admin/source directory on your computer system, execute the following statement:
CREATE DIRECTORY SOURCE_DIR AS '/usr/admin/source';
Use the SQL command
DIRECTORY to create a directory object for the directory into which you want to copy the binary file. For example, to create a directory object called
DEST_DIR for the
/usr/admin/destination directory on your computer system, execute the following statement:
CREATE DIRECTORY DEST_DIR AS '/usr/admin/destination';
Grant the required privileges to the user who will run the
COPY_FILE procedure. In this example, the
strmadmin user runs the procedure.
GRANT EXECUTE ON DBMS_FILE_TRANSFER TO strmadmin; GRANT READ ON DIRECTORY source_dir TO strmadmin; GRANT WRITE ON DIRECTORY dest_dir TO strmadmin;
strmadmin user and provide the user password when prompted:
COPY_FILE procedure to copy the file:
BEGIN DBMS_FILE_TRANSFER.COPY_FILE( source_directory_object => 'SOURCE_DIR', source_file_name => 'db1.dat', destination_directory_object => 'DEST_DIR', destination_file_name => 'db1_copy.dat'); END; /
Caution:Do not use the DBMS_FILE_TRANSFER package to copy or transfer a file that is being modified by a database because doing so may result in an inconsistent file.
Although the procedures in the
DBMS_FILE_TRANSFER package typically are invoked as local procedure calls, they can also be invoked as remote procedure calls. A remote procedure call lets you copy a file within a database even when you are connected to a different database. For example, you can make a copy of a file on database
DB, even if you are connected to another database, by executing the following remote procedure call:
Using remote procedure calls enables you to copy a file between two databases, even if you are not connected to either database. For example, you can connect to database
A and then transfer a file from database
B to database
C. In this example, database
A is the third party because it is neither the source of nor the destination for the transferred file.
A third-party file transfer can both push and pull a file. Continuing with the previous example, you can perform a third-party file transfer if you have a database link from
A to either
C, and that database has a database link to the other database. Database
A does not need a database link to both
For example, if you have a database link from
B, and another database link from
C, then you can run the following procedure at
A to transfer a file from
This configuration pushes the file.
Alternatively, if you have a database link from
C, and another database link from
B, then you can run the following procedure at database
A to transfer a file from
This configuration pulls the file.
You can use the
DBMS_SCHEDULER package to transfer files automatically within a single database and between databases. Third-party file transfers are also supported by the
DBMS_SCHEDULER package. You can monitor a long-running file transfer done by the Scheduler using the
V$SESSION_LONGOPS dynamic performance view at the databases reading or writing the file. Any database links used by a Scheduler job must be fixed user database links.
You can use a restartable Scheduler job to improve the reliability of file transfers automatically, especially if there are intermittent failures. If a file transfer fails before the destination file is closed, then you can restart the file transfer from the beginning once the database has removed any partially written destination file. Hence you should consider using a restartable Scheduler job to transfer a file if the rest of the job is restartable. See Chapter 27, "Scheduling Jobs with Oracle Scheduler" for more information on Scheduler jobs.
Note:If a single restartable job transfers several files, then you should consider restart scenarios in which some of the files have been transferred already and some have not been transferred yet.
You can create more sophisticated file transfer mechanisms using both the
DBMS_FILE_TRANSFER package and the
DBMS_SCHEDULER package. For example, when several databases have a copy of the file you want to transfer, you can consider factors such as source availability, source load, and communication bandwidth to the destination database when deciding which source database to contact first and which source databases to try if failures occur. In this case, the information about these factors must be available to you, and you must create the mechanism that considers these factors.
As another example, when early completion time is more important than load, you can submit a number of Scheduler jobs to transfer files in parallel. As a final example, knowing something about file layout on the source and destination databases enables you to minimize disk contention by performing or scheduling simultaneous transfers only if they use different I/O devices.