Loading

Wednesday, February 16, 2011

External Tables in Oracle with Example - Unloading Data using Datapump


In this post, let us see the step up step approach to unload data into an external file using external tables in oracle 10g

Step1: Create a Directory in OS
Since an external table's data is in the operating system, its data file needs to be in a place Oracle can access it. So the first step is to create a directory and grant access to it. First create the directory in the operating system, or choose an existing directory. It must be a real directory, not a symlink. Make sure that the OS user that the Oracle binaries run as has read-write access to this directory.




Step2: Create a Directory in Oracle linking to the Directory Created in STEP 1
Run the below script to Create a Directory in Oracle.



Step3: Run the below command in the source database to unload the data to an external file

CREATE TABLE export_emp_data ORGANIZATION EXTERNAL
  (
      TYPE ORACLE_DATAPUMP
      DEFAULT directory xtern_test_dir
      LOCATION ('emp_data.dmp')
  ) AS
SELECT * FROM emp;



Below screenshot will give you the details of data file created.




Step4: Run the below command in the destination database to load the data pumped out in the previous step to another external table.



 Step5: View the data


Further Reading:
Basics of External Tables: Loading data using external file example
http://shareoracleapps.blogspot.com/2011/02/what-is-external-tables-in-oracle-10g.html 

1 Responses to “External Tables in Oracle with Example - Unloading Data using Datapump”

LOKESH said...
April 30, 2011 at 2:32 AM

i am getting following error :
can u suggest solution for it.....
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file EXPORT_EMP_DATA_28835.log
OS error No such file or directory
ORA-06512: at "SYS.ORACLE_DATAPUMP", line 19


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.