External Tables - 10g

Until 9i external tables could only be used for read purposes.
But 10g allows you to populate external tables.

here is how it does:

Step 1: - Create a directory

SQL> create directory my_dir as '/usr/test'; --- Make sure Oracle OS user has
privilege to write on this directory.

Step 2: - Grant permission to user SCOTT

SQL> grant read,write on directory my_dir to scott;

Step 3: - Create the External Table: -

SQL> CREATE TABLE scott.external_emp_dept
ORGANIZATION EXTERNAL
(TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY my_dir
LOCATION ('emp_dept.exp')
)
reject limit unlimited
AS
select e.ename,d.dname from emp e, dept d where
e.deptno=d.deptno;

SQL> select * from scott.external_emp_dept;

ENAME DNAME
---------- --------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
MARTIN SALES
BLAKE SALES
CLARK ACCOUNTING
SCOTT RESEARCH
KING ACCOUNTING
TURNER SALES
ADAMS RESEARCH

ENAME DNAME
---------- --------------
JAMES SALES
FORD RESEARCH
MILLER ACCOUNTING

And you will find a file generated i.e. 'emp_dept.exp' in /usr/test directory.

Now you can take this file to the target database and create an external table
and associate the file with that table.

Step 4: - to be executed on the source database
SQL> set long 2000
SQL> Select dbms_metadata.get_ddl('TABLE','EXTERNAL_EMP_DEPT') from dual;

The above command will give you the DDL command to create the table at target
database.

Similar note is also available on metalink 249321.1 which was also written by me when I was with Oracle Support.