external tables can be implemented to directly access the format data in the directory file through the database object,The loading method is divided into two kinds of oracle_loader and Oracle_datapump,oracle_loader mode is loaded by SQLLDR engine, accessing flat format file;Oracle_datapump is loaded via the DataPump interface to access the DMP files uninstalled by Oracle_datapump mode;
------Oracle_loader----Customize the contents of two format files as follows: ====a.dat====360,jane,janus,st_clerk,121,17-may-2001,3000,0,50,jjanus361,mark,jasper,sa_rep, 145,17-may-2001,8000,.1,80,mjasper362,brenda,starr,ad_asst,200,17-may-2001,5500,0,10,bstarr363,alex,alda,ac_ MGR,145,17-MAY-2001,9000,.15,80,AALDA ====B.DAT====401,JESSE,CROMWELL,HR_REP,203,17-MAY-2001,7000,0,40, JCROMWEL402,ABBY,APPLEGATE,IT_PROG,103,17-MAY-2001,9000,.2,60,AAPPLEGA403,CAROL,COUSINS,AD_VP,100,17-MAY-2001, 27000,.3,90,ccousins404,john,richardson,ac_account,205,17-may-2001,5000,0,110,jrichard ---- Create the required directory connect / as sysdba;--Set up directories and grant access to hr create OR REPLACE directory Admin _dat_dir as '/u01/backup '; create OR REPLACE DIRECTORY admin_log_dir as '/u01/backup '; create OR REPLACE DIRECTORY admin_bad_dir as '/u01/ Backup '; grant READ on directory Admin_dat_dir to sun; grant WRITE in directory Admin_log_dir to Sun; grant WRITE on DIRECTORY admin_bad_dir to sun; ----create an external table by loader way, querying the operation directly from the table conn sun/sun--Create the External Tablecreate TABLE admin_ext_employees (employee_id number (4), first_name varchar2 (), last_name         VARCHAR2 (+), job_id varchar2 (Ten), manager_id number (4), hire_date DATE, salary number (8,2), commission_pct NUMBER (2,2), department_id number (4), email      VARCHAR2 (+) ) organization external ( type ORACLE_LOADER default DIRECTORY admin_dat_dir access parameters ( records Delimited by newline badfile admin_bad_dir: ' Empxt%a_%p.bad ' LogFile admin_log_dir: ' Empxt%a_%p.log ' fields Terminated by ', ' missing field values is null (employee_id, first_name, last_name, job_id, manager_id, hire_date Char date_format date Mask "dd-mon-yyyy", salary, commission_pct, department_id, email ) ) location (' A.dat ', ' B.dat ') ) parallel reject LIMIT UNLIMITED; create table EMP as SELECT * from Admin_ext_employees; SQL> SQL> SELECT * from emp; employee_id first_name last_name job_id m---------------------------------------------------------------- --- 401 Jesse cromwell hr_rep 402 Abby applegate it_prog 403 Carol cousins                AD_VP 404 John richardson ac_account 360 Jane Janus st_clerk 361 Mark jasper sa_rep 362 Brenda starr ad_asst 363 Alex alda ac_mgr SQL> SELECT * from User_ external_tables; no rows selected [[email protected] backup]$ ls-ltotal 20-rw-r--r--1 ora11 Oinstall 228 Sep 6 21:17 a.dat-rw-r--r--1 ora11 oinstall 252 Sep 6 21:17 b.dat-rw-r--r--1 ora11 Oinstall 2826 Sep 6 21:19 empxt000_2756.log-rw-r--r--1 ora11 oinstall 3110 Sep 6 21:19 empxt000_2763.log-rw-r --r--1 ora11 oinstall 3110 Sep 6 21:19 empxt001_2765.log
------oracle_datapump ----More is the ability to implement data migration, offload and migrate to other systems by datapump,----create external tables DataPump-way, and unload data into catalog Files;sql> CREATE TABLE Admin_ext_ Employees 2 (employee_id , 3 first_ Name , 4 last_name , 5 job_id , 6 manager_id , 7 hire_date , 8 salary 9 commission_pct , 10 department_id , email 12 ) 13 organization external 14 ( 15 type oracle_datapump 16 default DIRECTORY admin_dat_dir 17 location (' emp2.dmp ')) 18 as SELECT * from EMP; table created. ----Query the contents of the file, in general, the file is generated in XML format-rw-r-----1 ora11 oinstall 12288 Sep 6 21:33 emp2.dmp[[email& Nbsp;protected] backup]$ strings emp2.dmp "SUN". " U "X86_64/linux 2.4.xx ----Create external tables by loader way, query operations directly through Tables sql> CREATE TABLE admin_ext_employees2 2 (employee_id number (4), 3 first_name varchar2 (), 4 last_name VARCHAR2, 5 job_id varchar2 (Ten), 6 manager_id Number (4), 7 &nbsP; hire_date date, 8 salary number (8,2), 9 commission_pct number (2,2), 10 department_id number (4), 11 email    VARCHAR2 (+) 12 ) 13 organization EXTERNAL 14 ( 15 type oracle_datapump 16 default DIRECTORY admin_ Dat_dir 17 location (' emp2.dmp ')); Table created. SQL> SQL> SELECT * from Admin_ext_employees2; employee_id first_name & Nbsp; last_name job_id -------------------------------------------------------- ---------- 401 Jesse cromwell &nbsP; hr_rep 402 Abby applegate it_prog 403 Carol cousins              AD_VP 404 John richardson ac_account 360 Jane &Nbsp; janus st_clerk 361 Mark jasper sa_rep 362 Brenda starr ad_asst 363 Alex alda ac_mgr
Oracle_ two implementations of external tables Oracle_loader[datapump]
