Share the content if you found it is useful (You can share using 300 community websites) click "share" at the end of the post.

You are encouraged to leave a comment.








Thursday, February 12, 2009

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 NotesAll 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;

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;

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;

Checking the destination directory on the remote server will reveal that the file has been copied successfully.

2 comments:

Dinesh kumar said...

too good piece of information, I had come to know about your site from my friend sajid, bangalore,i have read atleast 11 posts of yours by now, and let me tell you, your web-page gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new post, once again hats off to you! Thanks a lot once again, Regards,obiee online training

silpa reddy said...

nice post.I found something new can get useful info about BEST obiee ONLINE TRAINING

Related Posts Plugin for WordPress, Blogger...

Let us be Friends...

Share |

Popular Posts

Labels

11.5.1. to 11.5.5 Cloning Procedure. 11g 11i 11i and R12 11i Autoconfig 11i Cloning 12.1.1 SSL 2 Node RAC Adconfig.sh errors out adgennls.pl adop Advanced Replication Apache version Apex Apex for Oracle database Apex Installation Apex Installation on Oracle E Business Suite. 11i with Apex Apex on 11i Apex on Oracle 11i Apex402 apexins.sql APPL_TOP Character Application Express Application server Issue APPLSYS password Apps Password Asynchronous Replication Autoconfig Autoconfig Managed Beehive Bit of operating system Block block media corrupt BPEL Central Inventory Character Set Conversion Cloning of 11i Cloning of Oracle Applications Clusterware Concurrent CPU 2011 CPU 2012 April CPU 2012 January. CPU 2012 October CRS custom top Custom Top Creation Database . Database Query Dataguard DBMS_REPAIR Demantra . Domain Name E Business Suite Migration EBS 12.2 EPM exp/imp Oracle Applications Flashback table flows_020200 FND_TOP/resource FNDCPASS FNDCPUCF Forms server upgrade Forms startup FRM-92101 Fusion Middleware Hostname Hot Backup Hot cloning HotBackup Hotbackup Cloning of Oracle Apps hrglobal.drv. NLS Saudi Arabian HRMS hyperion Import and Export Oracle Applications Install Oracle Installation of R12 Installation Steps integration Oracle EBS Issues Database issues Oracle EBS Issues with Upgrade 12.1.1 Java Mission Control Java Upgrade java/sql/SavePoint JInitiator Junk Characters Language Translation Linux Linux Migration listener trace Listner version Local Inventory Login Page Master-Master Replication Memory Tuning Multiple Language NLS Non-Autoconfig Enabled OBIEE 11g Installation OBIEE Issues OIM Opatch version OpenSSL ora-01031 Oracle oracle 11g Oracle Announcement Oracle Application servers Oracle Application services Oracle Applications Oracle Applications 11i oracle applications 12.2 Oracle Applications Cloning Oracle Applications Upgrade Oracle Applications. Oracle Apps cloning Oracle BPEL oracle certifications oracle database Oracle Database RMAN Oracle Database upgradation Oracle DB oracle E Business suite 12.2 Oracle EBS oracle EPM Oracle Forms Logo oracle Fusion Applications Oracle Inventory oracle news Oracle OUI Oracle R12 oracle Sun Solaris Oracle Tuning Oracle Virtual Box Oracle Webgate Oracle WMA configuration orcladmin password for Oracle Applications Pasta Configuration pasta.cfg patch PDF Performance Physical Standby Platform migration Printer Configuration Profile Option R12 RAC Rapid Install Rapidwiz Real Application Clusters 10g Recovery RMAN RMAN Recovery SA Gosi Service Oriented Architecture SOA SQLTXPLAIN SSL Configuration step-by-step upgrade Sun solaris Swap Tablespace Trace Enable Trace file Translation Synchronization Patch transport tablespace tuning Tuning SGA uifont.ali Underscore Universal Content Management updates.oracle.com Upgradation of 12.0.5 to 12.1.1 Upgrade Upgrade 11g Upgrade Apps Upgrade DB Upgrade to 11g UTF8 version of oracle applications versions Web server upgrade weblogic windows workflow version XML Publisher version