Oracle Copying Files Using the Database Server

Copying Files Using the Database Server

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".

The 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_TRANSFER package 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:

    Copying a File on a Local File System

    Third-Party File Transfer

    File Transfer and the DBMS_SCHEDULER Package

    Advanced File Transfer Mechanisms

    See Also:

        Oracle Streams Concepts and Administration

        "Transporting Tablespaces Between Databases"

        Oracle Database PL/SQL Packages and Types Reference for a description of the DBMS_FILE_TRANSFER package.

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:

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

    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;

    Connect as strmadmin user and provide the user password when prompted:

    CONNECT strmadmin

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