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.

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

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.
    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;

—— ——-  ——–   ——- ——— ——  —– ——-
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.

One thought on “Oracle Database : External Tables Overview and Usage

Want to give some comment to author ( Shivmohan Purohit )

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s