Oracle Database : External Tables Overview and Usage

What are External Tables?

External tables are like regular SQL tables with some exceptions:
          – The metadata of external tables is created using the SQL "CREATE TABLE … ORGANIZATION EXTERNAL" statement. 
          – The data resides outside the database in OS files, thus the EXTERNAL organization.
          – The OS files are identified inside the database through a logical directory defining the OS physical directory where they are located. The owner of the files is Oracle. Users granted access via the Database can access the files through the external table. 
          – The data is read only. 
          – You cannot perform any DML operations, nor create indexes. 
          – The external table can be queried and joined directly, in parallel using the SQL statement SELECT.

Example
——— 
A. Create the external table to extract the data without loading them inside the database

1. Create flat files emp1.dat

2 Create a directory that defines the location of the directory where the flat files reside on the OS
3 Create the external table (metadata)
4 Select data from the external table to verify that data are visible

• Create flat files emp1.dat

$ vi /u01/ora9i/data/emp1.dat
7369,SMITH,CLERK,7902,17-DEC-80,100,0,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,250,0,30
7521,WARD,SALESMAN,7698,22-FEB-81,450,0,30
7566,JONES,MANAGER,7839,02-APR-81,1150,0,20

Check OS level permissions and ensure the user has read/write privileges to this directory.

• Create a logical directory to map the OS directory where the external files reside and grant permissions to SCOTT

SQL> create directory emp_dir as ‘/u01/ora9i/data’ ;
Directory created.
SQL> GRANT READ ON DIRECTORY emp_dir TO scott;
Grant succeeded.
SQL> GRANT WRITE ON DIRECTORY emp_dir TO scott;
Grant succeeded.

• Create the external table :

SQL> create table scott.emp_ext
(emp_id number(4), ename varchar2(12),
job varchar2(12) , mgr_id number(4) ,
hiredate date, salary number(8), comm number(8),
dept_id number(2))
organization external
(type oracle_loader
default directory emp_dir
access parameters (records delimited by newline
fields terminated by ‘,’)
location (‘emp1.dat’);
Table created.

• Check the data retrieval:

SQL> select * from scott.emp_ext;

EMP_ID ENAME    JOB        MGR_ID  HIREDATE  SALARY  COMM  DEPT_ID
—— ——-  ——–   ——- ——— ——  —– ——-
7369   SMITH    CLERK      7902    17-DEC-80 100     0     20
7499   ALLEN    SALESMAN   7698    20-FEB-81 250     0     30
7521   WARD     SALESMAN   7698    22-FEB-81 450     0     30
7566   JONES    MANAGER    7839    02-APR-81 1150    0     20
7654   MARTIN   SALESMAN   7698    28-SEP-81 1250    0     30
7698   BLAKE    MANAGER    7839    01-MAY-81 1550    0     30
7934   MILLER   CLERK      7782    23-JAN-82 3500    0     10
7 rows selected.