DBMS_FILE_TRANSFER Package in Oracle Database 10g使用方法Oracle 10g has introduced the [font=NSimsun]DBMS_FILE_TRANSFER package which provides an API for copying binary files between database servers.
[list]
[*]Common Usage Notes
[*]COPY_FILE
[*]GET_FILE
[*]PUT_FILE
Common Usage NotesAll of the the currently supported procedures have some common usage notes listed below:
[list]
[*]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 [font=NSimsun]V$SESSION_LONGOPS view.
COPY_FILEThe [font=NSimsun]COPY_FILE procedure allows you to copy binary files from one location to another on the same server.
[indent]– 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;[/indent]Checking the destination directory will reveal that the file has been copied successfully.
GET_FILEThe [font=NSimsun]GET_FILE procedure allows you to copy binary files from a remote server to the local server.
[indent]– 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;[/indent]Checking the destination directory on the local server will reveal that the file has been copied successfully.
PUT_FILEThe [font=NSimsun]PUT_FILE procedure allows you to copy binary files from the local server to a remote server.
[indent]– 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;[/indent]