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:

sql> conn/as sysdbasql> Create directory External_dir as '/u01/app/oracle/oradata/external ';D irectory created. Sql> Grant Read,write on the directory External_dir to Kevin; Grant succeeded.

External table using user Kevin, creating an external table

sql> create table   ex_employees  2                       (employee_id        number (4),  3                       first_name         VARCHAR2 (,  4        )               last_name          VARCHAR2 (+),  5                       job_id             varchar2 (Ten),  6                       manager_id         number (4),  7                       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 external14         (15         type oracle_loader16          DEFAULT DIRECTORY external_dir17          ACCESS PARAMETERS18           (19           records delimited by  newline20           badfile external_dir: ' Empxt%a_%p.bad ' 21  &NBSp;        logfile  external_dir: ' Empxt%a_%p.log ' 22            fields terminated by  ', ' 23            missing field values are  null24            ( employee_id, first_name,  last_name, job_id, manager_id,25              hire_date char date_format date mask  "Dd-mon-yyyy",26              salary, commission_pct, department _id, email27           ) 28          ) 29         location  (' Example1.txt ',  ' example2.txt ') 30       ) 31       parallel32        REJECT LIMIT UNLIMITED; Table created.

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----------- -------------------- ------------------------- ------ ---- ---------- --------- ---------- -------------- ------------- -------------- -----------        360 Jane                  Janus                      ST_CLERK           121 17-may-01       3000               0             50 jjanus         361 mark                  Jasper                     sa_rep            145  17-MAY-01       8000              .1            80  mjasper        362 Brenda                starr                      AD_ASST            200 17-MAY-01       5500               0             10 bstarr        363 Alex                  Alda                        ac_mgr            145 17- may-01       9000             .15            80 aalda         401 Jesse                 Cromwell                   hr_rep            203 17- may-01       7000               0            40  jcromwel        402 Abby                  Applegate                  IT_PROG            103 17-may-01       9000              .2             60 aapplega        403 Carol                 Cousins                    AD_VP              100 17-MAY-01       27000             .3             90 ccousins         404 John                  richardsoN                ac_account         205 17-MAY-01        5000              0            110 jrichard10 rows selected.

Creation complete, external table can be used normally

This article is from the "DBA Sky" blog, so be sure to keep this source http://kevinora.blog.51cto.com/9406404/1690038

Oracle creates external tables