使用DBMS_FILE_TRANSFER包拷贝文件

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.（如sys用户）

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

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.