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); } } } |
