Create external table in oracle and create in oracle

Create external table in oracle and create in oracle

Oracle db allows you to query External tables in read-only mode. External tables can be stored on any storage device that can be read by oracle databases. The content of external tables is not saved in the database. db stores only the metadata of external tables, and db can query (join and sort) external table, which can create view and synonym, but cannot execute DML statements.

Create table syntax ...... Orginzition external: Suppose an external table as a view, and select can be performed normally.
Analyze and virtual column table analysis are not used with external tables

Create external table syntax
External table file:
Example1.txt
360, Jane, Janus, ST_CLERK, 2001-MAY-, 0, 50, jjanus
361, Mark, Jasper, SA_REP, 2001-MAY-8000,., mjasper
362, Brenda, Starr, AD_ASST, 2001-MAY-, 5500,0, 10, bstarr
363, Alex, Alda, AC_MGR, 2001-MAY-9000,., aalda
Example2.txt
401, Jesse, Cromwell, HR_REP, 203,17-MAY-2001, 0, 40, jcromwel
402, Abby, Applegate, IT_PROG, 2001-MAY-9000,., aapplega
403, Carol, Cousins, AD_VP, 2001-MAY-27000,., ccousins
404, John, Richard, AC_ACCOUNT, 2001-MAY-110, jrichard
Create an External table for oracle recognizable path:

SQL> conn / as sysdbaSQL> create directory external_dir as '/u01/app/oracle/oradata/external';Directory created.SQL> grant read，write on directory external_dir to kevin;Grant succeeded.

External table user kevin creates an External table

SQL> CREATE TABLE   ex_employees  2                     (employee_id       NUMBER(4),  3                      first_name        VARCHAR2(20),  4                      last_name         VARCHAR2(25),  5                      job_id            VARCHAR2(10),  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(25)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           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.

After creation, you can import data to the database table through cats. If there is a large amount of data, you can enable 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----------- -------------------- ------------------------- ---------- ---------- --------- ---------- -------------- ------------- -------------------------        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.

The External table is ready for use.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.