Data Movement with External table and datapump

Introduction

In this article we will see that with the help of external table and datapump we can move the Data across the Oracle database easily.

Introduced in 9i, external tables provided a new means of reading flat file data without first loading it into a staging table. Oracle 10g has taken external tables a stage further by enabling an external table to be created as a CTAS (Create Table As Select) operation, which enables a one-time unloading of data. Oracle has supplied a new access driver known as "ORACLE_DATAPUMP" which means that Oracle will "unload" an external table to a proprietary format usable by the Data Pump set of utilities (significant new 10g feature to replace imp and exp).

Source Database Name-DIAL
Target Database Name-ORCL

A simple demonstration.

We'll start with a simple demonstration of how to dump the data from the ALL_OBJECTS view to a file. I am assuming that readers are familiar with external table concepts, Datapump and DIRECTORY objects. Connect to the Source Database DIAL


SQL> CREATE DIRECTORY dial_dir AS 'D:\oracle\dial';

Directory created.

SQL> CREATE TABLE dial
  2  ORGANIZATION EXTERNAL
  3  (
  4     TYPE ORACLE_DATAPUMP
  5     DEFAULT DIRECTORY dial_dir
  6     LOCATION ('dial.dmp')
  7  )
  8  AS
  9     SELECT *
 10     FROM   all_objects;

Table created.

D:\oracle\dial>dir
 Volume in drive D is New Volume
 Volume Serial Number is 84B4-B4D9

 Directory of D:\oracle\dial

05/18/2006  04:53p                .
05/18/2006  04:53p                ..
05/18/2006  04:53p           4,923,392 dial.DMP
05/18/2006  04:53p                  45 dial_692_1872.log
               2 File(s)      4,923,437 bytes
               2 Dir(s)  29,286,920,192 bytes free

After the Creation of the external table is finish in Dial Database. Lets see the external table in Dial Database.


SQL> desc dial
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(30)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)

SQL> SELECT COUNT(*) FROM dial;

  COUNT(*)
----------
     47374

Moving the external table from Dial database to Orcl database from one schema to another with the help of datapump utility

Connect to the Orcl database as system user


SQL> CREATE DIRECTORY orcl_dir AS 'D:\oracle\Orcl';
SQL> grant read on directory orcl_dir to scott;
Grant succeeded.
SQL> grant write on directory orcl_dir to Scott;
Grant succeeded.
Now export the external table dial from the dial database.
Set ORACLE_SID=DIAL
C:\Documents and Settings\asood>expdp  system/******* DUMPFILE=test.dmp directory=dial_dir tables=dial
Export: Release 10.2.0.1.0 - Production on Thursday, 18 May, 2006 17:00:30
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production With the Partitioning, OLAP and Data Mining options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** DUMPFILE=test.dmp directory=dial_dir tables=dial
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
***********************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  D:\oracle\dial\TEST.DMP
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 17:00:48

Now we will import the dial table to Scott schema in Orcl Database. Copy the dial.dmp from d:\oracle\dial to d:\oracle\orcl (This is the main file where all the data is there for dial table). Copy the test.dmp from d:\oracle\dial to d:\oracle\orcl (This is the export file)


Import
Set ORACLE_SID=ORCL
C:\Documents and Settings\asood>impdp system/ankitsood directory=orcl_dir dumpfile=test.dmp remap_schema=system:scott

Import: Release 10.2.0.1.0 - Production on Thursday, 18 May, 2006 17:02:35
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=orcl_dir dumpfile=test.dmp remap_schema=system:scott
Processing object type TABLE_EXPORT/TABLE/TABLE
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 17:02:38

Now connect to orcl database as scott user 


SQL> show user
USER is "SCOTT"
SQL> SELECT COUNT(*) FROM dial;

  COUNT(*)
----------
     47374

Conclusions

Writeable external tables and Datapump are a useful new addition to Oracle 10g's set of utilities. They provide a fast a simple mechanism to backup or share critical and non-critical data in a variety of applications. This paper hopefully outlines some of the interesting new opportunities in 10G.

Resources

Following resources were referred during the analysis

  • [1] Oracle 10G Documentation
  • [2] www.metalink.oracle.com

About Me

I am working as Oracle Dba from past 5 years. I can be contacted at getsood@yahoo.com.