remap_table function of Oracle 11g R2 data Pump

Oracle has actually introduced this feature in 11.1.0.6, but there are bugs in 11.1.0.6. Here is a brief introduction to the Remap_table feature in 11.2.

Look at a simple example:

Sql> select * from V$version;

BANNER

--------------------------------------------------------------------------------

Oracle database11genterprise Edition release11.2.0.1.0-64bit Production

Pl/sql Release 11.2.0.1.0-production

CORE 11.2.0.1.0 Production

TNS for Linux:version 11.2.0.1.0-production

Nlsrtl Version 11.2.0.1.0-production

Sql> CREATE TABLE T

2 (ID number,

3 name VARCHAR2 (30));

Table has been created.

sql> INSERT INTO T

2 Select RowNum, Tname

3 from Tab;

11 lines have been created.

Sql> commit;

Submit completed.

Sql> CREATE INDEX ind_t_id

2 on t (ID);

The index has been created.

The following exports the T table:

Sql> host EXPDP yangtk/yangtk directory=d_output DUMPFILE=T.DP tables=t

Export:release11.2.0.1.0-production on Friday June 4 23:31:50 2010

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

Connect to: Oracle database11genterprise Edition release11.2.0.1.0-64bit Production

With the partitioning, OLAP, Data Mining and real application testing options

Start "YANGTK". Sys_export_table_01 ": yangtk/******** directory=d_output DUMPFILE=T.DP tables=t

Using the blocks method to estimate ...

Working with Object Types Table_export/table/table_data

Total estimate using the Blocks method: 128 KB

Working with Object Types table_export/table/table

Working with Object Types Table_export/table/index/index

Working with Object Types Table_export/table/index/statistics/index_statistics

. . "YANGTK" was exported. T "5.562 KB 11 lines

The primary table "YANGTK" was successfully loaded/unloaded. Sys_export_table_01 "

******************************************************************************

Yangtk. The set of dump files for sys_export_table_01 is:

/home/oracle/t.dp

The job "YANGTK". Sys_export_table_01 "completed successfully at 23:31:59

Use remap_table to remap the import table to the T_new table:

Sql> host IMPDP yangtk/yangtk directory=d_output DUMPFILE=T.DP remap_table=t:t_new

Import:release11.2.0.1.0-production on Friday June 4 23:32:19 2010

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

Connect to: Oracle database11genterprise Edition release11.2.0.1.0-64bit Production

With the partitioning, OLAP, Data Mining and real application testing options

The primary table "YANGTK" was successfully loaded/unloaded. Sys_import_full_01 "

Start "YANGTK". Sys_import_full_01 ": yangtk/******** directory=d_output DUMPFILE=T.DP remap_table=t:t_new

Working with Object Types table_export/table/table

Working with Object Types Table_export/table/table_data

. . "YANGTK" was imported. T_new "5.562 KB 11 lines

Working with Object Types Table_export/table/index/index

ORA-31684: Object type index: "YANGTK". " ind_t_id "already exists

Working with Object Types Table_export/table/index/statistics/index_statistics

ORA-39111: Skip Subordinate Object Type Index_statistics, base Object type index: "YANGTK". ind_t_id "already exists