External Tables - Pro Oracle Database 12c Administration - page 395

Database A

SQL> CREATE DIRECTORY
dp AS '/oradump';

INV table

CREATE TABLE inv_et
ORGANIZATION EXTERNAL (
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY dp
LOCATION ('inv.dmp')
) AS SELECT * FROM inv;

operating system
dump file
/oradump/inv.dmp

SQL> select *
from inv_et;

Database B

SQL> CREATE DIRECTORY
dp AS '/oradump'; copy to remote server

(scp, or ftp, ...)

SQL>create table
INV as select *
from inv_dw;

CREATE TABLE inv_dw
(inv_id number
,inv_desc varchar2(30))
ORGANIZATION EXTERNAL (
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY dp
LOCATION ('inv.dmp'));

operating system
dump file
/oradump/inv.dmp

SQL> select *
from inv_dw;

Figure 14-2. Using external tables to unload and load data

A small example illustrates the technique of using an external table to unload data. Here are the steps required:

1. Create a directory object that specifies where you want the dump file placed on disk. If
you're not using a DBA account, then grant read and write access to the directory object to
the database user that needs access.
2. Use the CREATE TABLE...ORGANIZATION EXTERNAL...AS SELECT statement to unload data
from the database into the dump file.
First, create a directory object. The next bit of code creates a directory object, named DP , that points to the
/oradump directory:

SQL> create directory dp as '/oradump';

If you're not using a user with DBA privileges, then explicitly grant access to the directory object to the
required user:

SQL> grant read, write on directory dp to larry;