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.(如sys用户)
-
Use the SQL command
CREATE DIRECTORY SOURCE_DIR AS '/usr/admin/source';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 calledSOURCE_DIR
for the/usr/admin/source
directory on your computer system, execute the following statement: -
Use the SQL command
CREATE DIRECTORY DEST_DIR AS '/usr/admin/destination';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 calledDEST_DIR
for the/usr/admin/destination
directory on your computer system, execute the following statement: -
Grant the required privileges to the user who will run the
GRANT EXECUTE ON DBMS_FILE_TRANSFER TO strmadmin;GRANT READ ON DIRECTORY source_dir TO strmadmin;GRANT WRITE ON DIRECTORY dest_dir TO strmadmin;COPY_FILE
procedure. In this example, thestrmadmin
user runs the procedure. -
Connect as
CONNECT strmadmin/strmadminpwstrmadmin
user: -
Run the
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;/COPY_FILE
procedure to copy the file:
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.