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