ORACLE_DATAPUMP & External Table

The ORACLE_DATAPUMP access driver unloads data from an Oracle database to a flat file (DPAPI format) using the external table method. The external table must be created using the CREATE TABLE ... AS SELECT ... (CTAS) method. You can specify the PARALLEL clause when creating
the table; ORACLE_DATAPUMP access driver unloads data into multiple flat files at the same time. One parallel execution server will write to only one file at a time. Unloading data in the context of external table means creating an external table (flat file) using CTAS method.

During the unload (or populate) operation, the data goes from the subquery to the SQL engine for the data to be processed and is extracted in the DPAPI format to write to the flat file.The external table to unload data can be created only using the CTAS method with the ORACLE_
DATAPUMP access driver. The unload operation does not include the metadata for the tables. You can use the VERSION clause when unloading the data to make sure it loads correctly on the target database.

There is an example about ORACLE_DATAPUMP & External Table,as follows:

[@more@]

[oracle@portal3115 ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Nov 26 10:26:00 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>
SQL>
SQL> create or replace directory dump_dir as '/oracle/oradata/ora10g/dump_dir';

Directory created.

SQL> grant all on directory dump_dir to shiyihai;

Grant succeeded.

SQL>
SQL> conn shiyihai/shiyihai
Connected.

SQL>
SQL> CREATE TABLE external_object
2 ORGANIZATION EXTERNAL (TYPE ORACLE_DATAPUMP
3 DEFAULT DIRECTORY dump_dir
LOCATION ('external_object_01.dmp','external_object_02.dmp'))
4 5 PARALLEL 2
AS
6 7 SELECT object_id,object_name,object_type,created,last_ddl_time,status
8 from all_objects;

Table created.

SQL> select count(*) from external_object;

COUNT(*)
----------
40689

SQL> select object_id,object_name,object_type,created,last_ddl_time,status from external_object where rownum<2;

OBJECT_ID OBJECT_NAME OBJECT_TYPE CREATED
---------- ------------------------------ ------------------- ------------
LAST_DDL_TIM STATUS
------------ -------
2362 ALL_JOIN_IND_COLUMNS VIEW 30-JUN-05
30-JUN-05 VALID

SQL>
SQL> desc external_object
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_ID NOT NULL NUMBER
OBJECT_NAME NOT NULL VARCHAR2(30)
OBJECT_TYPE VARCHAR2(19)
CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE
STATUS VARCHAR2(7)

SQL>
SQL>
SQL> CREATE TABLE second_external_object (
2 OBJECT_ID NUMBER,
OBJECT_NAME VARCHAR2(30),
3 4 OBJECT_TYPE VARCHAR2(19),
5 CREATED DATE,
6 LAST_DDL_TIME DATE,
STATUS VARCHAR2(7)
7 8 )
9 ORGANIZATION EXTERNAL (TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY dump_dir
10 11 ACCESS PARAMETERS (
12 LOGFILE 'second_external_object.log')
LOCATION ('external_object_01.dmp', 'external_object_02.dmp')
13 14 );

Table created.

SQL> select count(*) from second_external_object;

COUNT(*)
----------
40689

SQL>