今天碰到一个有意思的问题。在导入的时候如果采用表和索引分步导入的方式可能会出现问题。

看一个简单的例子：

SQL> CREATE TABLE T
2 (
3 ID NUMBER,
4 CONSTRAINT PK_T PRIMARY KEY (ID) USING INDEX (CREATE INDEX IND_T_ID ON T(ID))
5 );

Table created.

SQL> SELECT CONSTRAINT_NAME, INDEX_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'T';

CONSTRAINT_NAME INDEX_NAME
------------------------------ ------------------------------
PK_T IND_T_ID

SQL> INSERT INTO T SELECT ROWNUM FROM TAB;

27 rows created.

SQL> COMMIT;

Commit complete.

SQL> HOST exp test/test file=t.dmp tables=t

Export: Release 9.2.0.4.0 - Production on 星期四 4月 12 15:18:11 2007

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

Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table T 27 rows exported
Export terminated successfully without warnings.

SQL> DROP TABLE T;

Table dropped.

SQL> HOST imp test/test file=t.dmp tables=t

Import: Release 9.2.0.4.0 - Production on 星期四 4月 12 15:18:23 2007

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

Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing TEST's objects into TEST
. . importing table "T" 27 rows imported
Import terminated successfully without warnings.

SQL> SELECT CONSTRAINT_NAME, INDEX_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'T';

CONSTRAINT_NAME INDEX_NAME
------------------------------ ------------------------------
PK_T IND_T_ID

构建一个约束和索引名称不一样的表，对于正常的导入，不会有什么问题。但是，如果尝试使用下面的方法，将数据和索引分两次导入，则可能会出现下面的问题：

SQL> DROP TABLE T;

Table dropped.

SQL> HOST imp test/test file=t.dmp tables=t rows=y indexes=n

Import: Release 9.2.0.4.0 - Production on 星期四 4月 12 15:21:26 2007

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

Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing TEST's objects into TEST
. . importing table "T" 27 rows imported
Import terminated successfully without warnings.

SQL> HOST imp test/test file=t.dmp tables=t rows=n indexes=y ignore=y

Import: Release 9.2.0.4.0 - Production on 星期四 4月 12 15:21:32 2007

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

Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing TEST's objects into TEST
Import terminated successfully without warnings.

SQL> SELECT CONSTRAINT_NAME, INDEX_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'T';

CONSTRAINT_NAME INDEX_NAME
------------------------------ ------------------------------
PK_T PK_T

这是由于约束信息随表一起导入，而主键约束必须建立索引，因此Oracle自动创建了一个和约束同名的索引，等到导入索引的时候，由于相同字段上已经创建了索引，且指定了ignore=y，因此，IND_T_ID索引被忽略。

如果一定要采用分步的方式，可以将索引和约束放到一起：

SQL> HOST imp test/test file=t.dmp tables=t rows=y indexes=n constraints=n

Import: Release 9.2.0.4.0 - Production on 星期四 4月 12 15:25:03 2007

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

Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing TEST's objects into TEST
. . importing table "T" 27 rows imported
Import terminated successfully without warnings.

SQL> HOST imp test/test file=t.dmp tables=t rows=n indexes=y constraints=y ignore=y

Import: Release 9.2.0.4.0 - Production on 星期四 4月 12 15:25:10 2007

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

Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing TEST's objects into TEST
Import terminated successfully without warnings.

SQL> SELECT CONSTRAINT_NAME, INDEX_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'T';

CONSTRAINT_NAME INDEX_NAME
------------------------------ ------------------------------
PK_T IND_T_ID