Wednesday, February 16, 2011

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

CREATETABLE export_emp_data ORGANIZATIONEXTERNAL

(

TYPE ORACLE_DATAPUMP

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

Do you think this Article is useful?

i am getting following error : can u suggest solution for it.....ORA-29913: error in executing ODCIEXTTABLEOPEN calloutORA-29400: data cartridge errorKUP-04063: unable to open log file EXPORT_EMP_DATA_28835.log OS error No such file or directoryORA-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.