As you know we can load data from OS flat files into the Oracle environment,Now here showing how to create an external file / populating external table

Lets take a simple example: –

Step 1: – Create a directory

SQL> create directory my_dir as ‘/usr/abc’; —

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.extract_emp_dept



LOCATION (’emp.exp’))

reject limit unlimited


select e.ename,d.dname from emp e, dept d where


SQL> select * from scott.extract_emp_dept;

And you will find a file generated i.e. ’emp.exp’ in /usr/abc 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 performed on the source database

SQL> set long 2000

SQL> Select dbms_metadata.get_ddl(‘TABLE’,’EXTRACT_EMP_DEPT’) from dual;

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

Want to give some comment to author ( Shivmohan Purohit )

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s