How Oracle data pumps load indexes on tables that already exist

A friend mentioned a data pump import problem, in the presence of a table, do not delete the table, and import the table's data and indexes.

In fact, this task is very simple for imp, because IMP works in this way.

sql> CREATE TABLE T_exp

2 (ID number, NAME VARCHAR2 (30));

Table has been created.

sql> CREATE INDEX ind_t_exp_id

2 on T_exp (ID);

The index has been created.

Sql> INSERT into T_exp

2 SELECT rownum, Tname

3 from TAB;

72 lines have been created.

Sql> COMMIT;

Submit completed.

Sql> HOST exp test/test file=t_exp.dmp buffer=2048000 tables=t_exp

Export:release9.2.0.4.0-production on Wednesday June 2 15:12:26 2010

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connect to: oracle9i Enterprise Edition release9.2.0.4.0-production

With the partitioning, OLAP and Oracle Data Mining options

Jserver Release 9.2.0.4.0-production

Exported ZHS16GBK character set and al16utf16 nchar character set

About to export the specified table through the general path ...

. . exporting table T_exp 72 rows are exported

Successfully terminates the export without warning.

sql> DROP INDEX ind_t_exp_id;

The index has been discarded.

sql> HOST imp test/test file=t_exp.dmp buffer=2048000 tables=t_exp ignore=y

Import:release9.2.0.4.0-production on Wednesday June 2 15:13:10 2010

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connect to: oracle9i Enterprise Edition release9.2.0.4.0-production

With the partitioning, OLAP and Oracle Data Mining options

Jserver Release 9.2.0.4.0-production

To export a file created by export:v09.02.00 through a regular path

Import in the ZHS16GBK character set and AL16UTF16 nchar character set has been completed

. Importing the object of test into test

. . Importing table "T_exp" 72 rows are imported

The import was successfully terminated, but a warning appears.

Sql> SELECT COUNT (*) from T_exp;

COUNT (*)

----------

144

Sql> SELECT index_name

2 from User_indexes

3 WHERE table_name = ' t_exp ';

Index_name

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

ind_t_exp_id

The default work for IMPDP is not the case, and if the table is monitored, IMPDP will skip the creation of the index:

Sql> CREATE TABLE t_exp (ID number, NAME VARCHAR2 (30));

Table created.

Sql> INSERT into T_exp

2 SELECT rownum, Tname

3 from TAB;

Rows created.

Sql> COMMIT;

Commit complete.

sql> CREATE INDEX ind_t_exp_id

2 on T_exp (ID);

Index created.

The following export is performed:

[oracle@yans1 ~]$ EXPDP test/test directory=d_output DUMPFILE=T_EXP.DP

Export:release10.2.0.3.0-64bit Production on Wednesday, 02 June, 2010 15:18:59

Copyright (c) 2003, +, Oracle. All rights reserved.

Connected to:oracle database10genterprise Edition release10.2.0.3.0-64bit Production

With the partitioning, OLAP and Data Mining options

Starting "TEST". " Sys_export_table_01 ": test/******** directory=d_output DUMPFILE=T_EXP.DP tables=t_exp