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