ITPub博客

首页 > Linux操作系统 > Linux操作系统 > DBMS_FILE_TRANSFER Package in Oracle Database 10g

DBMS_FILE_TRANSFER Package in Oracle Database 10g

原创 Linux操作系统 作者:paulyibinyi 时间:2008-12-08 16:59:51 0 删除 编辑

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;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7199859/viewspace-507977/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
学习数据库

注册时间:2007-12-11

  • 博文量
    902
  • 访问量
    6565406