Creating external tables in oracle 10g.

External Tables lets you query data in a flat file as though the file were an Oracle table. In 10g, you can write out data to an external table, although you cannot write to an existing external table.

While external tables can be queried, they're not usable in many ways regular Oracle tables are. You cannot perform any DML operations on external tables other than table creation. One consequence is that you can't create an index on an external table. External tables are largely used as a convenient way of moving data into and out of the database.



Creating an external table


First create the directory in the operating system, or choose an existing directory. It must be a real directory. Make sure that the oracle user has read and write permissions on it.

The next step is to create these directories in Oracle, and grant read/write access on it to the Oracle user who will be creating the external table. When you create the directory, be sure to use the directory's full path, and don't include any symlinks in the path.

su – oracle

export ORACLE_SID=orcl

sqlplus

/ as sysdba

sql>  create or replace directory external_dir   
as ‘/u01/app/oracle/oradata/orcl’;   
                                       
directory created        # Message after running this command.

sql> grant read,write on directory external_dir to hr;

Grant Succeeded      # Message after running this command.

Now we will create the external file.

Connect as hr.

Export ORACLE_SID=orcl

sqlplus

username = hr
password = hr

sql> create table demo
organization external
( type oracle_datapump
default directory external_dir
location (‘demo123.dmp’)
) as select * from employees;


Table Created

Select * from demo;    # check the contents of the new table created as demo

Now we will copy the data to the scott users schema in another database from the above created external table.

su – oracle

export ORACLE_SID=window

sqlplus

scott/tiger

create table imp_demo
( employee_id   number(6),
first_name     varchar2(20),
last_name     varchar2(25),
email    varchar2(25),
phone_number  varchar2(20),
hire_date  date,
job_id     varchar2(10),
salary    number(8,2),
commission_pct     number(2,2)
manager_id     number(6),
department_id    number(4)
)
organization external
( type oracle_datapump
default directory data_dir
location (‘demo123.dmp’)
) ;

Table created    # message
Select * from imp_demo;   # check the contents.