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
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
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
1 Responses to “External Tables in Oracle with Example - Unloading Data using Datapump”
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