Oracle 12c DBMS_FILE_TRANSFER Demos

Oracle DBMS_FILE_TRANSFER Version 12.1.0.2

General Information

Library Note

Morgan's Library Page Footer

The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. 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.

Interested in Oracle GoldenGate? Check out the IGGOUG,

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

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;