12c_DBMS_FILE_TRANSFER

发表于 2015 年 02 月 17 日 由 Travel.liu

1.DBMS_FILE_TRANSFER

The DBMS_FILE_TRANSFER package provides procedures to copy a binary file within a database or to transfer a binary file between databases. The destination database converts each block when it receives a file from a platform with different endianness. Datafiles can be imported after they are moved to the destination database as part of a transportable operation without RMAN conversion. Both GET and PUT operations will converted the file across platform difference at the destination. However, COPY is a local operation and therefore no conversion is required.

2.DBMS_FILE_TRANSFER.GET_FILE

Contacts a remote DATABASE TO READ a remote file AND THEN creates a copy OF the file IN the LOCAL file system
 
conn system/password@DB1
-- create database link
CREATE DATABASE LINK DB2
CONNECT TO system@remote
IDENTIFIED BY password USING 'DB2';
 
-- create the destination directory object and a database link
CREATE OR REPLACE DIRECTORY DIR_1 AS '/u02/oradata/db12c/';
 
conn system/password@DB2
 
-- create the destination directory object and a database link
CREATE OR REPLACE DIRECTORY DIR_2 AS '/u02/oradata/db12c/';
 
 
-- get the file
BEGIN
  DBMS_FILE_TRANSFER.GET_FILE(source_directory_object =>
  'DIR_2', source_file_name => 'users01.dbf',
  source_database => 'DB2', destination_directory_object =>
  'DBR_1', destination_file_name => 'users01.dbf');
END;
/

3.DBMS_FILE_TRANSFER.PUT_FILE

Reads a LOCAL file AND contacts a remote DATABASE TO CREATE a copy OF the file IN the remote file system
-- LOGIN TO THE REMOTE SERVER
CONN SYSTEM/PASSWORD@DB2
 
-- CREATE THE DESTINATION DIRECTORY OBJECT
CREATE OR REPLACE DIRECTORY DRI_1 AS '/U02/ORADATA/DB12C/';
 
-- LOGIN TO THE LOCAL SERVER.
CONN SYSTEM/PASSWORD@DB1
 
-- CREATE THE SOURCE DIRECTORY OBJECT, DATABASE LINK AND SWITCH TABLESPACE MODE
CREATE OR REPLACE DIRECTORY DRI_2 AS '/U01/ORADATA/DB12C/';
CREATE DATABASE LINK DB2 CONNECT TO SYSTEM IDENTIFIED BY PASSWORD USING 'DB2';
 
ALTER TABLESPACE USERS READ ONLY;
 
-- PUT THE FILE
BEGIN
  DBMS_FILE_TRANSFER.PUT_FILE(SOURCE_DIRECTORY_OBJECT =>
 'DRI_1', SOURCE_FILE_NAME =>  'USERS01.DBF',
  DESTINATION_DIRECTORY_OBJECT => 'DRI_2',
  DESTINATION_FILE_NAME => 'USERS01.DBF',
  DESTINATION_DATABASE => 'DB2');
END;
/
 
-- SWITCH THE TABLESPACE BACK TO READ WRITE MODE
ALTER TABLESPACE USERS READ WRITE;

4.XTTS

XTTS中的DBMS_FILE_TRANSFER方法就是使用这个功能

            debug "$user: @{$transfer{$user}}\n";
            foreach my $file (@{$transfer{$user}})
            {
               # Bug 17673476: If the destination file is for ASM, it does not
               # work with automated filenames. So we convert the names here.
               # The automated filenames will be of format "x.y.z". This will
               # converted to "x_y_z"
               my $destFile = $file;
               if ($destFile =~ m/(.*)\.([0-9]+)\.([0-9]+)/)
               {
                  $destFile = "$1_$2_$3";
               }
               $sqlQuery = "
                  BEGIN
                  DBMS_FILE_TRANSFER.PUT_FILE(
                  source_directory_object      => '".$props{'srcdir'}."',
                  source_file_name             => '".$file."',
                  destination_directory_object => '".$props{'dstdir'}."',
                  destination_file_name        => '".$destFile."',
                  destination_database         => '".$props{'dstlink'}."');
                  END;
                  /
                  quit;
                  ";
               ## Invoke the backup as copy routine once generated as the last step
               $output = `sqlplus -L -s $connectstring <<EOF
                                  $sqlSettings
                                  $sqlQuery
                                  quit;
                                  EOF
                                  `;
               checkError ("Error in executing \n$sqlQuery\n", $output);
 
 
   foreach my $x (@getArray)
   {
      chomp ($x);
      if ($x =~ m/(.*?),(.*?),(.*)/)
      {
         my $sqlQuery =
                 "BEGIN
                  DBMS_FILE_TRANSFER.GET_FILE(
                  source_directory_object      => '".$props{'srcdir'}."',
                  source_file_name             => '".$2."',
                  source_database              => '".$props{'srclink'}."',
                  destination_directory_object => '".$props{'dstdir'}."',
                  destination_file_name        => '".$3."');
                  END;
                  /
                  ";
 
         $getFileTemp = "getfile"."_$2"."_$1".".sql";
         open FILE, ">$getFileTemp";
         print FILE "$sqlQuery\nquit\n";
         close FILE;
 
         ChecktoProceed($getParallel);
         $pid = fork();
 
         if ($pid == 0)
         {
            PrintMessage ("Executing getfile for $getFileTemp");
            $output =  `sqlplus -L -s \"$connectstringcnvinst\" \@$getFileTemp`;
            checkError ("Error in executing $getFileTemp", $output);
            Unlink ($getFileTemp);
            exit (0);
         }
         else
         {
            UpdateForkArray ($pid, $getParallel);
         }
      }
   }
此条目发表在 Oracle安装升级 分类目录。将固定链接加入收藏夹。

评论功能已关闭。