8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
DBMS_FILE_TRANSFER Package in Oracle Database 10g
Oracle 10g has introduced the DBMS_FILE_TRANSFER
package which provides an API for copying binary files between database servers.
Common Usage Notes
All of the the currently supported procedures have some common usage notes listed below.
- The user must have read privilege on the source directory object and write privilege on the destination directory object.
- The procedure converts directory object names to uppercase unless they are surrounded by double quotes.
- Files to be copied must be multiples of 512 bytes in size.
- Files to be copied must be equal to or less than 2 terabytes in size.
- File transfers are not transactional.
- Files are copied as binary, so no character conversions are performed.
- File copies can be monitored using the
V$SESSION_LONGOPS
view.
COPY_FILE
The COPY_FILE
procedure allows you to copy binary files from one location to another on the same server.
-- Create the source and destination directory objects. CREATE OR REPLACE DIRECTORY db_files_dir1 AS '/u01/oradata/DB10G/'; CREATE OR REPLACE DIRECTORY db_files_dir2 AS '/u02/oradata/DB10G/'; -- Switch a tablespace into read only mode so we can -- use it for a test file transfer. ALTER TABLESPACE users READ ONLY; -- Copy the file. BEGIN DBMS_FILE_TRANSFER.copy_file( source_directory_object => 'DB_FILES_DIR1', source_file_name => 'USERS01.DBF', destination_directory_object => 'DB_FILES_DIR2', destination_file_name => 'USERS01.DBF'); END; / -- Switch the tablespace back to read write mode. ALTER TABLESPACE users READ WRITE;
Checking the destination directory will reveal that the file has been copied successfully.
GET_FILE
The GET_FILE
procedure allows you to copy binary files from a remote server to the local server.
-- Login to the remote server. CONN system/password@remote -- Create the source directory object and switch mode of a tablespace. CREATE OR REPLACE DIRECTORY db_files_dir1 AS '/u01/oradata/DB10G/'; ALTER TABLESPACE users READ ONLY; -- Login to the local server. CONN system/password@local -- Create the destination directory object and a database link. CREATE OR REPLACE DIRECTORY db_files_dir2 AS '/u02/oradata/DB10G/'; CREATE DATABASE LINK remote CONNECT TO system IDENTIFIED BY password USING 'REMOTE'; -- 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;
Checking the destination directory on the local server will reveal that the file has been copied successfully.
PUT_FILE
The PUT_FILE
procedure allows you to copy binary files from the local server to a remote server.
-- 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 mode of a tablespace. 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;
Checking the destination directory on the remote server will reveal that the file has been copied successfully.
For more information see:
Hope this helps. Regards Tim...