Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

August 9, 2007

Unload Data with External Tables and Data Pump

Filed under: Oracle 10g New Features,Oracle How To — H.Tonguç Yılmaz @ 9:26 am

Before 10g External Tables were used to read external data into destination database but after Oracle 10g external tables can be created as a CTAS(Create Table As Select) operation, which enables a one-time unloading of inside data.

This is NOT a native data unloader(no more sqlplus spools, UTL_FILE writes, Pro*C unloaders etc). Oracle supplied a new access driver called “ORACLE_DATAPUMP” and Oracle will “unload” an external table to a format only usable by the Data Pump utility another new 10g feature to replace traditional imp and exp. So generating an ASCII or CSV file from external tables is NOT possible, at least for today :)

Below is a simple demonstration of how to dump the data from the ALL_SOURCE view to a file.

Code Listing 107-External Table Write Demo

The log file can be avoided using the NOLOGFILE keyword, but contains usefull information such as the time the file was opened and any error messages that occur during the creation or read-back of the external table.

For more information and examples please see this fantastic paper as usual by Oracle ACE Howard RogersCreating External Tables with Data Pump

Testing Information : the scripts mentioned are tested on Oracle Database 10g Enterprise Edition Release

Refences Used :
Oracle® Database Utilities 10g Release 2 (10.2) – Chapter 1 Overview of Oracle Data Pump – External Tables Part


  1. The new feature is pretty good, but still not able to unload into ASCII like fastreader does.
    I am curious what is the reason of that. so fastreader http://www.wisdomforce.com complements it well when a lot of data has to be extracted quickly into flat files or ascii pipes and ways like spool or utl_file are not an option because of performance

    Comment by dbaoracle — October 25, 2007 @ 4:44 pm | Reply

  2. Additional info also after 10g; Transferring a File to a Different Database(10g ASM aware)

    SOURCE_FILE_NAME => ‘exm_old.txt’,
    DESTINATION_FILE_NAME => ‘exm_new.txt’

    In order to transfer a file the other way around, you must replace the PUT_FILE procedure with the GET_FILE procedure.

    You can monitor copying progress using V$SESSION_LONGOPS view.

    Comment by H.Tonguç Yılmaz — May 25, 2008 @ 7:47 pm | Reply

  3. — a dblink example
    show release
    release 1002000300

    create database link gg_source connect to usernm identified by passwd

    select * from global_name@gg_source ;

    host mkdir /tmp/tong

    CREATE or REPLACE DIRECTORY ext_tab_dir AS ‘/tmp/tong’;

    set timing on

    CREATE TABLE ext_all_source
    DEFAULT DIRECTORY ext_tab_dir
    LOCATION ( ‘all_source_sorted1.dmp’, ‘all_source_sorted2.dmp’,
    ‘all_source_sorted3.dmp’, ‘all_source_sorted4.dmp’ )
    SELECT * FROM all_source@gg_source ORDER BY 1,2;

    Elapsed: 00:00:42.09

    SELECT /*+ PARALLEL(t,4) */ COUNT(*) FROM ext_all_source t;


    Elapsed: 00:00:00.10

    CREATE TABLE tab_all_source
    SELECT /*+ PARALLEL(t,4) */ * FROM ext_all_source t;

    SELECT /*+ PARALLEL(t,4) */ COUNT(*) FROM tab_all_source t;


    Elapsed: 00:00:00.14

    select sum(bytes)/(1024*1024) MB from dba_segments
    where segment_name = ‘TAB_ALL_SOURCE’ ;


    host ls -lt /tmp/tong/*.dmp
    -rw-r—– 1 oracle dba 19222528 May 26 18:05 /tmp/tong/all_source_sorted1.dmp
    -rw-r—– 1 oracle dba 19263488 May 26 18:05 /tmp/tong/all_source_sorted2.dmp
    -rw-r—– 1 oracle dba 19218432 May 26 18:05 /tmp/tong/all_source_sorted3.dmp
    -rw-r—– 1 oracle dba 19255296 May 26 18:05 /tmp/tong/all_source_sorted4.dmp

    /* to clean up
    drop database link gg_source ;
    DROP DIRECTORY ext_tab_dir ;
    DROP TABLE ext_all_source PURGE ;
    DROP TABLE tab_all_source PURGE ;
    host rm -rf /tmp/tong

    Comment by H.Tonguç Yılmaz — May 26, 2008 @ 3:24 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: