Please visit www.oracle-class.com for Videos, Free posts, Books, Webinar and Free forum … ALL about Oracle!

Uploading data into an external file

Actually, we have different MySql Servers managing different wedsite databases. Main database is on Oracle 10G R2.
We are using flat files to synchronize between heteregenous DBMS. After testing, we realize that it’s better to use UTL_FILE to fastest spool esternal flat files than simple spool from Sql*plus.

SQL *Plus is a client program which is accessing Oracle database. Therefore it highly depends on the network. If the network traffic to the server is slow, then SQL *Plus will perform slower. No doubt. If the server and client are in same machine expect SQL*Plus to perform faster. Sql* plus is using the network and Listener layer.
whereas, UTL_FILE is a server-side program residing within the Oracle database server. Use this procedure if you need to spool something in the server. This will perform faster in such cases. In client machine it always depends on the network load. Whether you are using SQL *Plus or UTL_FILE the network and listener status load is always a factor to check with.

If you are attenpting to synchronize data between one or more Oracle 10G or 11G databases, then Oracle 10g or 11G let you create a new external table from data in your database, which goes into a flat file pushed from the database using the ORACLE_DATAPUMP access driver. This flat file is in an Oracle-proprietary format that can be read only by DataPump. The main difference is that you must specify the access driver, ORACLE_DATAPUMP, since the access driver defaults to ORACLE_LOADER.

Below an example :

create table my_export_
organization external
( type oracle_datapump
default directory TEMP_DIR
location (‘converters.dmp’)
) as select * from advmain.converters ;

[oracle@test tmp]$ ls
converters.dmp
[oracle@test tmp]$

1 Comment »

  1. Sure!

    Comment by orawiss — November 13, 2010 @ 5:31 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: