Which has the higher priority in your organization: Deploying a new database or securing the ones you already have?
Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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.
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;