Dev's Weblog

Reading time: 3 - 5 minutes

Often DBAs are needed to import the data of one table of first database to another table of another database.

With 10G DBAs had to export the table and import it to destination DB then rename the table.

But with 11G you can simply REMAP the table to new table name of destination Database.

As for Example,

Source DB : CHICAGO

$sqlplus dev/oracle@CHICAGO

SQL>create table dev as select rownum num from dual connect by rownum <= 1000000;

Check the size of table after its creating using following SQL.

col segment_name format a20
select segment_name,bytes "SIZE_BYTES",ceil(bytes / 1024 / 1024) "SIZE_MB" from   dba_segments where  segment_name = 'DEV';

SEGMENT_NAME         ?SIZE_BYTES?  ?SIZE_MB?
--------------------         ------------         ----------
DEV                           13631488         13

1. Export DEV table from DEV schema.

expdp userid=dev/oracle dumpfile=DATA_PUMP_DIR:exp.dmp tables=dev reuse_dumpfiles=y

Reuse_dumpfiles will re-create the dumpfile if the same file exists.This is mostly helpful in case of ASM where you need remove the dumpfile from SQLPLUS if you don't have access to ASMCMD.

Export: Release 11.2.0.1.0 - Production on Tue Sep 8 12:01:23 2009

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "DEV"."SYS_EXPORT_TABLE_01":  userid=dev/******** dumpfile=DATA_PUMP_DIR:exp.dmp tables=dev reuse_dumpfiles=y
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 13 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "DEV"."DEV"                                 8.568 MB 1000000 rows
Master table "DEV"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DEV.SYS_EXPORT_TABLE_01 is:
  /u01/app/dev/admin/CHICAGO/dpdump/exp.dmp
Job "DEV"."SYS_EXPORT_TABLE_01" successfully completed at 12:01:33

Destination DB : DUP

1. Import the Dev table to Het table.Please note that Het table must not exists before you import it.

impdp userid=dev/oracle dumpfile=DATA_PUMP_DIR:exp.dmp remap_table=dev.dev:het

Import: Release 11.2.0.1.0 - Production on Tue Sep 8 12:07:33 2009

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "DEV"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "DEV"."SYS_IMPORT_FULL_01":  userid=dev/******** dumpfile=DATA_PUMP_DIR:exp.dmp remap_table=dev.dev:het
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DEV"."HET"                                 8.568 MB 1000000 rows
Job "DEV"."SYS_IMPORT_FULL_01" successfully completed at 12:07:38

Notice that the REMAP_TABLE option is designated as

[schema.]old_tablename[.partition]:new_tablename.

• Check Het table created or not.

dev@shetal:~$ sqlplus  dev/oracle@DUP

SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 8 12:08:10 2009

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> desc het
Name                       Null?    Type
----------------------------------------- -------- ----------------------------
NUM                            NUMBER

SQL> select count(*) from het;

  COUNT(*)
----------
   1000000

Enjoy Madi…