Oracle creates external tables

Oracle DB allows external tables to be queried in read-only form. The external table can be stored in any storage device that can be read by Oracle DB, its contents are not saved in db, the DB only saves external table metadata,db can query (join, Sort) External table, you can create a view, Synonym, but DML statements cannot be executed.

Creating external table syntax create TABLE ... orginzition external, you can think of an external table as a view, you can do a normal select.
Analyze and virtual list analysis are not used with external tables

Create external Table syntax
External Table file:
Example1.txt
360,jane,janus,st_clerk,121,17-may-2001,3000,0,50,jjanus
361,mark,jasper,sa_rep,145,17-may-2001,8000,.1,80,mjasper
362,brenda,starr,ad_asst,200,17-may-2001,5500,0,10,bstarr
363,alex,alda,ac_mgr,145,17-may-2001,9000,.15,80,aalda
Example2.txt
401,jesse,cromwell,hr_rep,203,17-may-2001,7000,0,40,jcromwel
402,abby,applegate,it_prog,103,17-may-2001,9000,.2,60,aapplega
403,carol,cousins,ad_vp,100,17-may-2001,27000,.3,90,ccousins
404,john,richardson,ac_account,205,17-may-2001,5000,0,110,jrichard
To create an external table Oracle recognized path:

ascreateas‘/u01/app/oracle/oradata/external‘read，writeonto kevin;Grant succeeded.

External table using user Kevin, creating an external table

Sql> CREATETABLEEx_employees 2 (employee_idNumber (4), 3 first_nameVARCHAR2 (), 4 last_nameVARCHAR2 (+), 5 job_idVARCHAR2 (ten), 6 manager_idNumber (4), 7 hire_date date, 8 salaryNumber (8,2), 9 commission_pctNumber (2,2), Ten department_idNumber (4), emailVARCHAR2 (+)ORGANIZATION EXTERNAL14 (TYPE oracle_loader16DEFAULTDIRECTORY external_dir17 ACCESS PARAMETERS18 (Records delimited by Newline20 Badfil E External_dir:' Empxt%a_%p.bad ' +LogFile External_dir:' Empxt%a_%p.log ' AFields terminated by', ' atMissing 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 -) -) inLocation (' Example1.txt ',' Example2.txt ') -) toPARALLEL +REJECT LIMIT UNLIMITED;TableCreated.

After the creation, you can import data into the database table by cats, if the data is more, you can open session level parallel import

Alter Session Enable parallel;Create Table Employee  as Select *  from Ex_employee;employee_id first_name           last_name                 job_id     manager_id hire_date     SALARY commission_pct department_id EMAIL----------- -------------------- ------------------------- ---------- ---------- --------- ---------- -------------- ------------- -------------------------         the Jane                 Janus                     St_clerk          121  -- May- on        the              0             - Jjanus        361 Mark                 Jasper                    Sa_rep            145  -- May- on       8000             .1             the Mjasper        362 Brenda               Starr                     Ad_asst            $  -- May- on       5500              0            Ten Bstarr        363 Alex                 Alda                      Ac_mgr            145  -- May- on       9000            . the             the Aalda        401 Jesse                Cromwell                  Hr_rep            203  -- May- on       7000              0             + Jcromwel        402 Abby                 Applegate                 It_prog           103  -- May- on       9000             .2             - Aapplega        403 Carol                Cousins                   AD_VP              -  -- May- on      27000             .3             - Ccousins        404 John                 Richardson                Ac_account        205  -- May- on        the              0            the JrichardTen rows selected.

Creation complete, external table can be used normally

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Oracle creates external tables