DBMS_FILE_TRANSFER package which provides an API for copying binary files between database servers

jidongzheng發表於2011-01-26

轉:

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.
[@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

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;

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/197458/viewspace-1045201/,如需轉載,請註明出處,否則將追究法律責任。

相關文章