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 OSSince 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
DEFAULT directory xtern_test_dir
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
Basics of External Tables: Loading data using external file example
Do you think this Article is useful?
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.