Oracle DBMS_FILE_TRANSFER
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
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
 
COPY_FILE
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;

Related Topics
DBMS_TTS
Directory
Packages
Transportable Tablespaces
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved