Oracle have incorporated support for data pump technology into external tables.
The ORACLE_DATAPUMP access driver can be used to unload data to data pump export files and subsequently reload it.
The unload of data occurs when the external table is created using the “AS” clause.
CREATE TABLE emp_xt ORGANIZATION EXTERNAL ( TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY test_dir LOCATION ('emp_xt.dmp') ) AS SELECT * FROM emp;
The data can then be queried using the following.
SELECT * FROM emp_xt;
The syntax to create the external table pointing to an existing file is similar, but without the “AS” clause. In this case we will do it the same schema, but this could be in a different schema in the same instance, or in an entirely different instance.
DROP TABLE emp_xt; CREATE TABLE emp_xt ( 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)) ORGANIZATION EXTERNAL ( TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY test_dir LOCATION ('emp_xt.dmp') );
SELECT * FROM emp_xt;
Creating an external table using the ORACLE_DATAPUMP access driver is restricted to dump files created by the external table unload.