External Tables - 10g

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 hasprivilege 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 RESEARCHALLEN SALESWARD SALESJONES RESEARCHMARTIN SALESBLAKE SALESCLARK ACCOUNTINGSCOTT RESEARCHKING ACCOUNTINGTURNER SALESADAMS RESEARCHENAME DNAME---------- --------------JAMES SALESFORD RESEARCHMILLER 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.