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
ORGANIZATION EXTERNAL
(TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY my_dir
LOCATION (’emp.exp’))
reject limit unlimited
AS
select e.ename,d.dname from emp e, dept d where
e.deptno=d.deptno;
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.
One thought