CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_FILE_TRANSFER
Version 11.1
 
General Information
Note: Provides procedures to copy a binary file within a database or to transfer a binary file between databases.
Source {ORACLE_HOME}/rdbms/admin/dbmsxfr.sql
First Available 10.1
Constants package_flag BINARY_INTEGER;
Dependencies
dbms_file_transfer_lib dbms_sys_error
dbms_snap_internal dbms_utility
dbms_streams_tablespace_adm global_name
dbms_streams_tbs_int gv$session_longops
Error Messages invalid_parameter EXCEPTION;
PRAGMA exception_init(invalid_parameter, -31600);
invalid_parameter_num NUMBER := -31600;
Object Privileges GRANT EXECUTE ON dbms_file_transfer TO <schema_name>;
 
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 / as sysdba

CREATE OR REPLACE DIRECTORY SOURCE_DIR AS 'c: emp\source';

CREATE OR REPLACE DIRECTORY DEST_DIR AS 'c: emp\dest';

CREATE TABLESPACE filetxfr LOGGING
DATAFILE 'c: emp\source iletxfr1.dbf' SIZE 5120K
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;

DROP TABLESPACE filetxfr 
INCLUDING CONTENTS AND DATAFILES;

--/////// need to complete this part //////--

CREATE USER bcd
IDENTIFIED BY bcd
DEFAULT TABLESPACE filetxfr
TEMPORARY TABLESPACE temp
QUOTA 0 ON system
QUOTA 0 ON sysaux
QUOTA UNLIMITED ON filetxfr;

GRANT create session TO bcd;
GRANT create table TO bcd;

GRANT execute ON DBMS_FILE_TRANSFER TO bcd;
GRANT read ON DIRECTORY source_dir TO bcd;
GRANT write ON DIRECTORY dest_dir TO bcd;

conn bcd/bcd

CREATE TABLE t
TABLESPACE filetxfr AS
SELECT * FROM all_tables;

CREATE TABLE i
TABLESPACE filetxfr AS
SELECT * FROM all_indexes;

conn / as sysdba

SELECT file_name, bytes
FROM dba_data_files
WHERE TABLESPACE_NAME = 'FILETXFR';

ALTER TABLESPACE filetxfr OFFLINE NORMAL;

--=======================================

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;
/

DROP TABLESPACE filetxfr INCLUDING CONTENTS AND DATA FILES;
 
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 / as sysdba

CREATE TABLESPACE filetxfr LOGGING
DATAFILE 'c: emp\source iletxfr1.dbf' SIZE 5120K
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;

-- create database link
CREATE DATABASE LINK remote 
CONNECT TO system 
IDENTIFIED BY password USING 'REMOTE';

-- add to tnsnames.ora

-- create directory
CREATE OR REPLACE DIRECTORY remote_dir AS 'c: emp emote';

-- 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: emp emote';

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/DB10G/';

-- 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/DB10G/';

-- 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/DB10G/';
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
Directory
Transportable Tablespaces
UTL_FILE
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [39 users online]    © 2010 psoug.org