Oracle DBMS_FILE_TRANSFER
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
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_FILE_TRANSFER_LIB DBMS_STREAMS_TBS_INT DBMS_UTILITY
DBMS_SNAP_INTERNAL DBMS_SYS_ERROR GLOBAL_NAME
DBMS_STREAMS_TABLESPACE_ADM    
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      VARCHAR2,
source_file_name             VARCHAR2,
destination_directory_object VARCHAR2,
destination_file_name        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      VARCHAR2,
source_file_name             VARCHAR2,
source_database              VARCHAR2,
destination_directory_object VARCHAR2,
destination_file_name        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      VARCHAR2,
source_file_name             VARCHAR2,
destination_directory_object VARCHAR2,
destination_file_name        VARCHAR2,
destination_database         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

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-2014 Daniel A. Morgan All Rights Reserved