Transferring Operating System Files from within Oracle

As new versions of Oracle are released, we are less
likely to access the operating system. The ability to move data files from
within Oracle is just one more great hook to the operating system that aids DBAs
in their daily tasks.

Within Oracle 10g we are given the opportunity to move
operating system files without ever ‘physically’ logging into the operating
system itself. This in itself provides us, as DBAs, many options when doing common
database administrative tasks that we will typically do. These tasks range from
simple storage management, where we need to free up space on a disk, to
allowing us to move tablespaces and associated data files to other databases.
If you have any scripts lying around that do file movement such as backups or
cloning of databases this option should spark some interest from you.

DBMS_FILE_TRANSFER

As this package suggests it provides you the ability to move
(transfer) operating system files from one location to another. The beauty of
this package is that it allows you to move these operating system files either
within the current database host environment or between database hosts. Table A
gives you a brief description of the procedures that are included in this
package and what they do.

DBMS_FILE_TRANSFER Procedures

Here are the current three procedures that are packaged in
the DBMS_FILE_TRANSFER package and the associated definition.

COPY_FILE Procedure

Allows you to read an operating system file (source_file_name)
within a directory (source_directory_object) on the local database host
environment and create a copy (destination_file_name) in another directory (destination_directory_object)
on the same local host system.

Syntax


DBMS_FILE_TRANSFER.COPY_FILE(
source_directory_object IN VARCHAR2,
source_file_name IN VARCHAR2,
destination_directory_object IN VARCHAR2,
destination_file_name IN VARCHAR2);

GET_FILE Procedure

Allows you to read in a file (source_file_name) within a
directory (source_directory_object) from a remote database host system (source_database)
and create a copy of that data file (destination_file_name) in a directory (destination_directory_object)
on the current local host database system

Syntax


DBMS_FILE_TRANSFER.GET_FILE
source_directory_object IN VARCHAR2,
source_file_name IN VARCHAR2,
source_database IN VARCHAR2,
destination_directory_object IN VARCHAR2,
destination_file_name IN VARCHAR2);

PUT_FILE Procedure

Allows you to read in a file (source_file_name) within a
directory (source_directory_object) from the current local database host system
and create a copy of that data file (destination_file_name) in a directory (destination_directory_object)
on a remote database host system (desination_database).

Syntax


DBMS_FILE_TRANSFER.PUT_FILE(
source_directory_object IN VARCHAR2,
source_file_name IN VARCHAR2,
destination_directory_object IN VARCHAR2,
destination_file_name IN VARCHAR2,
destination_database IN VARCHAR2);

Input Definitions

Here is a brief definition of the input for each of the
procedures within the DBMS_FILE_TRANSFER package.

source_directory_object

This is a created directory object where a file resides from which you wish to copy a file. This is done through the CREATE DIRECTORY command and then read privileges must be granted to the user on this directory.

source_file_name

This
is the name of a file that exists within the source_directory_object
directory that you wish to copy either to the current host database system or
to a remote destination host database system.

source_database

This
is a database link that needs to be created with the CREATE DATABASE LINK
command and is the source database where a source data file exists that you
wish to copy to a remote system.

destination_directory_object

A
source data file will be placed into this created directory object. This
directory is created through the CREATE DIRECTORY command. Write privileges
must be granted to the user on this directory.

destination_file_name

This
is the name of the file you wish to copy the source file to, within the destination_directory_object
directory. There must not be a file in this directory that matches the name
you are trying to write.

destination_database

This
database link needs to be created with the CREATE DATABASE LINK command and
represents the destination database to which you are copying the data file.

Privileges Required

In order to read and write to the directories on the source and destination hosts you must grant the following to the users that will be executing the DBMS_FILE_TRANSFER procedures. If you are worried about giving someone complete access to the operating system files that might have sensitive information you will need to create extra directory structures and then grant privileges according to the type of information stored in those directories.

  • You must grant READ privileges for users on the
    DIRECTORY used in the source_directory_object if you wish them to copy files
    from this directory.

  • You must grant WRITE privileges for users on the
    DIRECTORY used in the destination_directory_object if you wish them to copy
    files into this directory.

Considerations

When using the DBMS_FILE_TRANSFER package there are a couple of considerations and usage notes of which you should be aware.

  1. The procedures contained within the DBMS_FILE_TRANSFER package will convert the parameters to uppercase unless you use double quotes around the variable.
  2. Not to worry, the file names are not converted to uppercase.
  3. When a file is copied, it is treated as a binary file.
  4. No character conversions are performed when copying files.
  5. File size must be a multiple of 512 bytes.
  6. File size cannot exceed 2 terabytes.
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles