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
(TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY my_dir
reject limit unlimited
select e.ename,d.dname from emp e, dept d where
SQL> select * from scott.external_emp_dept;
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
Similar note is also available on metalink 249321.1 which was also written by me when I was with Oracle Support.