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 Rogers – Creating External Tables with Data Pump
Testing Information : the scripts mentioned are tested on Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Refences Used :
Oracle® Database Utilities 10g Release 2 (10.2) – Chapter 1 Overview of Oracle Data Pump – External Tables Part
Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog 
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
Additional info also after 10g; Transferring a File to a Different Database(10g ASM aware)
BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(
SOURCE_DIRECTORY_OBJECT => ‘SOURCE_DIR’,
SOURCE_FILE_NAME => ‘exm_old.txt’,
DESTINATION_DIRECTORY_OBJECT => ‘DEST_DIR’,
DESTINATION_FILE_NAME => ‘exm_new.txt’
DESTINATION_DATABASE => ‘US.ACME.COM’);
END;
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.
— a dblink example
show release
release 1002000300
create database link gg_source connect to usernm identified by passwd
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST= .. ) .. ))’;
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
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY ext_tab_dir
LOCATION ( ‘all_source_sorted1.dmp’, ‘all_source_sorted2.dmp’,
‘all_source_sorted3.dmp’, ‘all_source_sorted4.dmp’ )
)
PARALLEL 4
AS
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;
COUNT(*)
———-
740697
Elapsed: 00:00:00.10
CREATE TABLE tab_all_source
NOLOGGING PARALLEL 4
AS
SELECT /*+ PARALLEL(t,4) */ * FROM ext_all_source t;
SELECT /*+ PARALLEL(t,4) */ COUNT(*) FROM tab_all_source t;
COUNT(*)
———-
740697
Elapsed: 00:00:00.14
select sum(bytes)/(1024*1024) MB from dba_segments
where segment_name = ‘TAB_ALL_SOURCE’ ;
MB
———-
98.25
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
*/
Using external table preprocessor – http://blogs.oracle.com/warehousebuilder/2009/06/file_staging_using_external_table_preprocessor.html