Oracle External table

Oracle External table
1. Introduction to external tables External tables are used after Oracle9i. An External table is a type of table that is defined in the database and data does not exist in the database. You can select, join, or sort External tables during database operations, or create views and synonyms for external tables. However, you cannot perform DML operations or create indexes on External tables. The External table provides two access drivers. One ORACLE_LOADER and the other ORACLE_DATAPUMP. The default driver is ORACLE_LOADER. The ORACLE_LOADER driver reads data from external files. The syntax for creating external tables using ORACLE_LOADER is similar to that of SQL * Loader utility. ORACLE_DATAPUMP first loads data from the External table and then reload the table from the External table. The following sections describe the details. In addition, the Statistical Information Collection of External tables supports the DBMS_STATS package, but does not support ANALYZE. In addition, external tables do not support virtual columns. 2. Create an External table

[Oracle @ localhost mydir] $ cat samp1.txt 360, Jane, Janus, ST_CLERK, 121,17-MAY-2001, 0, 50, jjanus361, Mark, Jasper, SA_REP, 2001-MAY-8000 ,. 2001, mjasper362, Brda, Starr, AD_ASST, 2001-17-MAY-9000, 5500,0, 10, bstarr363, Alex, Alda, AC_MGR, 145, 17-MAY ,. 15,80, aalda401, Jesse, Cromwell, HR_REP, 203,17-MAY-2001, 2001, 9000, 40, jcromwel402, Abby, Applegate, IT_PROG,-MAY ,. 2001, aapplega403, Carol, Cousins, AD_VP, 27000-17-MAY ,. 2001, ccousins404, John, Richard, AC_ACCOUNT, 110-MAY-, jrichard SQL> create or replace directory mydir as '/home/oracle/mydir'; Directory created. SQL> grant read, write on directory mydir to scott; Grant succeeded. SQL> conn scott/tigerConnected. create table admin_ext_employees (employee_id NUMBER (4), first_name VARCHAR2 (20), last_name VARCHAR2 (25), job_id VARCHAR2 (10), manager_id NUMBER (4), hire_date DATE, salary NUMBER (8, 2), commission_pct NUMBER (2, 2), department_id NUMBER (4), email VARCHAR2 (25 )) organization external (TYPE ORACLE_LOADER default directory mydir access parameters (records delimited by newline badfile mydir: 'badxt % a _ % p. bad 'logfile mydir: 'logxt % a _ % p. log 'fields terminated by ', 'missing field values are 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 ('samp1.txt ') parallel reject limit unlimited; PARALLEL -- specify the degree of parallelism when querying reject limit unlimited -- specify the number of external Table query errors

 

Check the logs generated after successful execution.

[oracle@localhost mydir]$ ls -altotal 20drwxr-xr-x 2 oracle oinstall 4096 Nov 20 16:12 .drwx------ 5 oracle oinstall 4096 Nov 20 15:53 ..-rw-r--r-- 1 oracle oinstall 1413 Nov 20 16:12 logxt000_13688.log-rw-r--r-- 1 oracle oinstall 1555 Nov 20 16:12 logxt000_16408.log-rw-r--r-- 1 oracle oinstall  480 Nov 20 15:53 samp1.txt[oracle@localhost mydir]$ cat logxt000_13688.log  LOG file opened at 11/20/15 16:12:03Field Definitions for table ADMIN_EXT_EMPLOYEES  Record format DELIMITED BY NEWLINE  Data in file has same endianness as the platform  Rows with all null fields are accepted  Fields in Data Source:     EMPLOYEE_ID                     CHAR (255)      Terminated by ","      Trim whitespace same as SQL Loader    FIRST_NAME                      CHAR (255)      Terminated by ","      Trim whitespace same as SQL Loader    LAST_NAME                       CHAR (255)      Terminated by ","      Trim whitespace same as SQL Loader    JOB_ID                          CHAR (255)      Terminated by ","      Trim whitespace same as SQL Loader    MANAGER_ID                      CHAR (255)      Terminated by ","      Trim whitespace same as SQL Loader    HIRE_DATE                       CHAR (80)      Date datatype DATE, date mask dd-mon-yyyy      Terminated by ","      Trim whitespace same as SQL Loader    SALARY                          CHAR (255)      Terminated by ","      Trim whitespace same as SQL Loader    COMMISSION_PCT                  CHAR (255)      Terminated by ","      Trim whitespace same as SQL Loader    DEPARTMENT_ID                   CHAR (255)      Terminated by ","      Trim whitespace same as SQL Loader    EMAIL                           CHAR (255)      Terminated by ","      Trim whitespace same as SQL Loader[oracle@localhost mydir]$ cat logxt000_16408.log  LOG file opened at 11/20/15 16:12:03Field Definitions for table ADMIN_EXT_EMPLOYEES  Record format DELIMITED BY NEWLINE  Data in file has same endianness as the platform  Rows with all null fields are accepted  Fields in Data Source:     EMPLOYEE_ID                     CHAR (255)      Terminated by ","      Trim whitespace same as SQL Loader    FIRST_NAME                      CHAR (255)      Terminated by ","      Trim whitespace same as SQL Loader    LAST_NAME                       CHAR (255)      Terminated by ","      Trim whitespace same as SQL Loader    JOB_ID                          CHAR (255)      Terminated by ","      Trim whitespace same as SQL Loader    MANAGER_ID                      CHAR (255)      Terminated by ","      Trim whitespace same as SQL Loader    HIRE_DATE                       CHAR (80)      Date datatype DATE, date mask dd-mon-yyyy      Terminated by ","      Trim whitespace same as SQL Loader    SALARY                          CHAR (255)      Terminated by ","      Trim whitespace same as SQL Loader    COMMISSION_PCT                  CHAR (255)      Terminated by ","      Trim whitespace same as SQL Loader    DEPARTMENT_ID                   CHAR (255)      Terminated by ","      Trim whitespace same as SQL Loader    EMAIL                           CHAR (255)      Terminated by ","      Trim whitespace same as SQL LoaderDate Cache Statistics for table ADMIN_EXT_EMPLOYEES  Max Size:      1000  Entries :         1  Hits    :         7  Misses  :         0

 

Logxt000_16408.log contains more statistical information than logxt000_13688.log. View the External table SQL> select EMPLOYEE_ID, FIRST_NAME from ADMIN_EXT_EMPLOYEES; EMPLOYEE_ID FIRST_NAME ----------- -------------------- 360 Jane361 mark#brenda363 Alex401 Jesus Abby403 Carol404 John 3. to modify an External table, you can use the clauses below alter TABLE to modify the access parameter reject limit: alter table admin_ext_employees reject limit n; project column: ALTER table admin_ext_employees project column referenced; alter table ‑ project column all; default dectory: alter table admin_ext_employees default directory admin_dat2_dir; view the modified attribute SQL> select OWNER, TABLE_NAME, REJECT_LIMIT, DEFAULT_DIRECTORY_NAME, PROPERTY from dba_external_tables; OWNER TABLE_NAME REJECT_LIM DEFAULT_DIRECTORY_NAME PROPERTY ---------- -------------------------------- ---------- SCOTT ADMIN_EXT_EMPLOYEES unlimited mydir all 4. preprocessing External table SQL> create or replace directory mydir as '/home/oracle/mydir'; Directory created. SQL> grant read, write, execute on directory mydir to scott; Grant succeeded. create a file under/home/oracle/mydir

[oracle@localhost mydir]$ cat uncompress /bin/gzip -cd $1[oracle@localhost mydir]$ chmod +x uncompressSQL> conn scott/tiger   Connected.CREATE TABLE admin_ext_employees                   (employee_id       NUMBER(4),                     first_name        VARCHAR2(20),                    last_name         VARCHAR2(25),                     job_id            VARCHAR2(10),                    manager_id        NUMBER(4),                    hire_date         DATE,                    salary            NUMBER(8,2),                    commission_pct    NUMBER(2,2),                    department_id     NUMBER(4),                    email             VARCHAR2(25)                    )      ORGANIZATION EXTERNAL      (        TYPE ORACLE_LOADER        DEFAULT DIRECTORY mydir        ACCESS PARAMETERS        (          records delimited by newline PREPROCESSOR exec_file_dir:'uncompress'  badfile mydir:'badxt%a_%p.bad'          logfile mydir:'logxt%a_%p.log'          fields terminated by ','          missing field values are 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 ('samp1.txt.gz')      )     PARALLEL                      REJECT LIMIT UNLIMITED;

 

5. to delete an External TABLE, use the drop table statement to delete only the media in the database, which does not affect the actual data. SQL> drop table admin_ext_employees; Table dropped. 6. system permissions and objects for external tables. System permission for external tables create any tablealter any tabledrop any tableselect any table object permission for external tables ALTERSELECT