Oracle 12cR2 DBMS_FILE

Oracle DBMS_FILE_TRANSFER Version 12.2.0.1

General Information

Library Note

Morgan's Library Page Header

Are you prepared for the release of Oracle Database 18c ... the first autonomous database? We are here at the Library. It is time for DBAs to stop fighting robots with their fingers and losing ... time to start using our intelligence and winning.

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

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 10gR1

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

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;