Today I rediscovered a feature of Oracle that is in since 10i. In 12c it was extended.

What is it all about? I am able to use dumps that where created with data pump as a file for external tables. But not enough I can also create an external table using CTAS (create table as select). Let’s see the simple steps to do so.

First I need a directory to write into. There are two options. First I grant the right as seen below to the user in need or I create the directory with the privileged user and grant the right to the directory to the user.

GRANT CREATE ANY DIRECTORY TO SCOTT;

As SCOTT I may now create the need directory. Oracle needs access to that directory.

CREATE OR REPLACE DIRECTORY ext_dir AS '/u01/userhome/oracle/ext_tables';

Directory EXT_DIR created.

Next I may create the external table with the data pump access driver. This is done with “TYPE ORACLE_DATAPUMP”.

CREATE TABLE ext_emp
  ORGANIZATION EXTERNAL
    (
      TYPE ORACLE_DATAPUMP
      DEFAULT DIRECTORY ext_dir
      LOCATION ('ext_emp.dmp')
    )
    AS SELECT * FROM emp;

Table EXT_EMP created.

Now I can already access the external table

SELECT * FROM ext_emp;

 EMPNO ENAME      JOB          MGR HIREDATE     SAL   COMM     DEPTNO
------ ---------- --------- ------ --------- ------ ------ ----------
  7369 SMITH      CLERK       7902 17-DEC-80    800                20
  7499 ALLEN      SALESMAN    7698 20-FEB-81   1600    300         30
  7521 WARD       SALESMAN    7698 22-FEB-81   1250    500         30
  7566 JONES      MANAGER     7839 02-APR-81   2975                20
  7654 MARTIN     SALESMAN    7698 28-SEP-81   1250   1400         30
  7698 BLAKE      MANAGER     7839 01-MAY-81   2850                30
  7782 CLARK      MANAGER     7839 09-JUN-81   2450                10
  7788 SCOTT      ANALYST     7566 19-APR-87   3000                20
  7839 KING       PRESIDENT        17-NOV-81   5000                10
  7844 TURNER     SALESMAN    7698 08-SEP-81   1500      0         30
  7876 ADAMS      CLERK       7788 23-MAY-87   1100                20
  7900 JAMES      CLERK       7698 03-DEC-81    950                30
  7902 FORD       ANALYST     7566 03-DEC-81   3000                20
  7934 MILLER     CLERK       7782 23-JAN-82   1300                10

14 rows selected. 

The definition is the same as the table was.

DESC ext_emp

Name     Null? Type         
-------- ----- ------------ 
EMPNO          NUMBER(4)    
ENAME          VARCHAR2(10) 
JOB            VARCHAR2(9)  
MGR            NUMBER(4)    
HIREDATE       DATE         
SAL            NUMBER(7,2)  
COMM           NUMBER(7,2)  
DEPTNO         NUMBER(2)    

Some additional thoughts:

• No change of data possible. Once created it is read only
• Parallel is possible
• Compression is possible but only with “Advanced Compression”

It is a simple way to use external tables. No need to think about the format. No need to think about conversions. It is all done for you. Isn’t this great.

For more information just visit:
Oracle External Table Documentation
Oracle External Table Documentation – Oracle Data Pump Access Driver