Oracle 18cR3 DBMS_FILE

Oracle DBMS_FILE_TRANSFER Version 18.3.0.1

General Information

Library Note

Morgan's Library Page Header

For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.

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

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

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

Subprograms

COPY_FILE

GET_FILE

PUT_FILE

COPY_FILE (new 18c Overload)

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;