Oracle External Table Population小记

从效率上，external table type=oracle_datapump>external table type=oracle_loader>oracle sqlldr direct. 

另外external table population相对于data pump具有更强的ETL功能：Although Data Pump Export and Import canuse the newexternal tableaccess driver, they are not able tohandle all the complex extract, transform, load(ETL) cases. For example, you can manually create an external table that can be used to unload the result of a complexjoin between many source tables. The generated flat files can then be used to load and transform thecorresponding dataintothe target system by using SQL commandsdirectly. 

今天试验了下External Table Population(Type=ORACLE_DATAPUMP),记录如下：

Step1---Createdirectory

sqlplus system/sys@testdb;

SQL>createdirectory extable as‘/data/extable’;（该目录已存在且可被当前操作系统用户读写）

SQL>grant read,write on extable to whf;

Step2---Unloading

sqlplus whf/whf@testdb;

CREATETABLE emp_ext

(first_name,last_name,department_name) --指定列名

ORGANIZATIONEXTERNAL  --指定使用外部表

(

TYPE ORACLE_DATAPUMP                     --指定类型为ORACLE_DATAPUMP

DEFAULTDIRECTORY extable                  --指定路径

LOCATION(‘emp1.exp’,‘emp2.exp’,‘emp3.exp’)     --指定生成的文件

)

PARALLEL     4                                                   --并行度取location与parallel的最小值:如果Location文件数大于并行度则多余文件被忽略，如果location文件数小于并行度，则并行度自动降为文件数目.               

AS

SELECTe.first_name,e.last_name,d.department_name

FROMemployees e, departments d

WHEREe.department_id =d.department_idAND

d.department_name in

('Marketing','Purchasing');

›Step3 --- Loading

SQL>create table  target_table_nameasselect* fromext_tbl;