Copying Files Using the Database Server - Oracle 10g

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.

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.

If the source file is an operating system file, then the destination file must also be an operating system file. Similarly, if the source file is an Automatic Storage Management file, then the destination file must also be an Automatic Storage Management file.

This section contains the following topics:

• Copying a File on a Local File System
• Third-Party File Transfer
• File Transfer and the DBMS_SCHEDULER Package
• Advanced File Transfer Mechanisms

Copying a File on a Local File System

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:

1. In SQL*Plus, connect as an administrative user who can grant privileges and create directory objects using SQL.
2. 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';
3. Use the SQL command CREATE 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';
4. 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;
5. Connect as strmadmin user:
   CONNECT strmadmin/strmadminpw
6. Run the 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; /

Third-Party File Transfer

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 B or C, and that database has a database link to the other database. Database A does not need a database link to both B and C. For example, if you have a database link from A to B, and another database link from B to C, then you can run the following procedure at A to transfer a file from B to C:

This configuration pushes the file.

Alternatively, if you have a database link from A to C, and another database link from C to B, then you can run the following procedure at database A to transfer a file from B to C:

This configuration pulls the file.

File Transfer and the DBMS_SCHEDULER Package

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.

Advanced File Transfer Mechanisms

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.