Oracle | DBMS_FILE

Oracle DBMS_FILE_TRANSFER Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.

Purpose Provides procedures to copy a binary file (BFILE) within a database or to transfer a binary file between databases. File sizes must be a multiple of 512K.

For purposes of these demos assume that "remote" is a PDB on a different server though it could easily be a PDB within the same container.

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
package_flag	BINARY_INTEGER	0

Data Types package_flag BINARY_INTEGER;

Dependencies

DBMS_ASSERT	DBMS_STANDARD	DBMS_SYS_ERROR
DBMS_FILE_TRANSFER_LIB	DBMS_STREAMS_TABLESPACE_ADM	GLOBAL_NAME
DBMS_SNAP_INTERNAL	DBMS_STREAMS_TBS_INT

Documented Yes

Exceptions

Error Code	Reason
ORA-31600	invalid_parameter

First Available 10.1

Security Model Owned by SYS with EXECUTE granted to the EXECUTE_CATALOG_ROLE role

Source {ORACLE_HOME}/rdbms/admin/dbmsxfr.sql

Subprograms

Reads a local file and creates a copy of it in the local file system

Overload 1 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);

conn sys@pdbdev as sysdba

      

      CREATE OR REPLACE DIRECTORY SOURCE_DIR AS 'c:\temp\source';

      

      CREATE OR REPLACE DIRECTORY DEST_DIR AS 'c:\temp\dest';

      

      CREATE TABLESPACE filetxfr LOGGING

      DATAFILE 'c:\temp\source\filetxfr1.dbf' SIZE 200M

      BLOCKSIZE 8192

      EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M

      SEGMENT SPACE MANAGEMENT AUTO;

      

      ALTER TABLESPACE filetxfr READ ONLY;

      

      BEGIN

        dbms_file_transfer.copy_file(source_directory_object =>

        'SOURCE_DIR', source_file_name => 'filetxfr1.dbf',

        destination_directory_object  => 'DEST_DIR',

        destination_file_name => 'filetxfr1.dbf');

      END;

      /

      

      ALTER TABLESPACE filetxfr READ WRITE;

      

      SELECT tablespace_name, file_name

      FROM dba_data_files;

      

      DROP TABLESPACE filetxfr INCLUDING CONTENTS AND DATAFILES;

Overload 2 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, created_file_name OUT VARCHAR2, destination_file_tag IN VARCHAR2 DEFAULT 'COPY_FILE');

TBD

GET_FILE

Contacts a remote database to read a remote file and then creates a copy of the file in the local file system 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);

-- login to the local machine

      conn sys@pdbdev as sysdba

      

      CREATE TABLESPACE filetxfr LOGGING

      DATAFILE 'c:\temp\source\filetxfr1.dbf' SIZE 200M

      BLOCKSIZE 8192

      EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M

      SEGMENT SPACE MANAGEMENT AUTO;

      

      -- create database link

      CREATE DATABASE LINK remote

      CONNECT TO system@remote

      IDENTIFIED BY password USING 'REMOTE';

      

      -- add to tnsnames.ora

      

      -- create directory

      CREATE OR REPLACE DIRECTORY remote_dir AS 'c:\temp\remote';

      

      -- login to the remote server

      conn system/password@remote

      

      -- create the source directory object and switch tablespace mode

      CREATE OR REPLACE DIRECTORY remote_dir AS 'c:\temp\remote';

      

      ALTER TABLESPACE filetxfr READ ONLY;

      

      -- login to the local server

      conn system/password

      

      -- create the destination directory object and a database link

      CREATE OR REPLACE DIRECTORY db_files_dir2 AS '/u02/oradata/db12c/';

      

      -- get the file

      BEGIN

        dbms__file_transfer.get_file(source_directory_object =>

        'DB_FILES_DIR1', source_file_name => 'users01.dbf',

        source_database => 'REMOTE', destination_directory_object =>

        'DB_FILES_DIR2', destination_file_name => 'users01.dbf');

      END;

      /

      

      -- login to the remote server

      CONN system/password@remote

      

      -- switch the tablespace back to read write mode

      ALTER TABLESPACE users READ WRITE;

PUT_FILE

Reads a local file and contacts a remote database to create a copy of the file in the remote file system 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);

-- login to the remote server

conn system/password@remote

      

      -- create the destination directory object

      CREATE OR REPLACE DIRECTORY db_files_dir2 AS '/u02/oradata/db12c/';

      

      -- Login to the local server.

      CONN system/password@local

      

      -- create the source directory object, database link and switch tablespace mode

      CREATE OR REPLACE DIRECTORY db_files_dir1 AS '/u01/oradata/db12c/';

      CREATE DATABASE LINK remote CONNECT TO system IDENTIFIED BY password USING 'REMOTE';

      

      ALTER TABLESPACE users READ ONLY;

      

      -- put the file

      BEGIN

        dbms_file_transfer.put_file(source_directory_object =>

       'DB_FILES_DIR1', source_file_name =>  'users01.dbf',

        destination_directory_object => 'DB_FILES_DIR2',

        destination_file_name => 'users01.dbf',

        destination_database => 'REMOTE');

      END;

      /

      

       -- switch the tablespace back to read write mode

      ALTER TABLESPACE users READ WRITE;