转:
DBMS_FILE_TRANSFER Package in Oracle Database 10g
Oracle 10g has introduced theDBMS_FILE_TRANSFER
package which provides an API for copying binary files between database servers.
[@more@]
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
TheCOPY_FILE
procedure allows you to copy binary files from one location to another on the same server.
Checking the destination directory will reveal that the file has been copied successfully.-- 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;
GET_FILE
TheGET_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;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/197458/viewspace-1045201/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/197458/viewspace-1045201/