表和索引分步imp时，如表上有主键约束时就要注意了，导入表时不要同时导入约束，约束待每二步和索

引同时建立才能保证索引的名字是正确的。

SQL> conn eric/eric
Connected.

SQL> create table t(
2 id number,
3 constraint pk_t primary key(id) using index (create index ind_t_id on t(id))
4 );

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;

1 row created.

SQL> commit;

Commit complete.

SQL> host exp eric/eric@orcl file=t.dmp tables=t

Export: Release 10.2.0.1.0 - Production on Thu Jan 8 13:09:52 2009

Copyright (c) 1982, 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
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

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

SQL> drop table t;

Table dropped.

表删除后先导入表
SQL> host imp eric/eric@orcl file=t.dmp tables=t rows=y indexes=n

Import: Release 10.2.0.1.0 - Production on Thu Jan 8 13:11:15 2009

Copyright (c) 1982, 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

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing ERIC's objects into ERIC
. importing ERIC's objects into ERIC
. . importing table "T" 1 rows imported
Import terminated successfully without warnings.

再导入索引
SQL> host imp eric/eric@orcl file=t.dmp tables=t rows=n indexes=y ignore=y

Import: Release 10.2.0.1.0 - Production on Thu Jan 8 13:12:03 2009

Copyright (c) 1982, 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

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing ERIC's objects into ERIC
. importing ERIC's objects into ERIC
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 eric/eric@orcl file=t.dmp tables=t rows=y indexes=n constraints=n

Import: Release 10.2.0.1.0 - Production on Thu Jan 8 13:13:37 2009

Copyright (c) 1982, 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

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing ERIC's objects into ERIC
. importing ERIC's objects into ERIC
. . importing table "T" 1 rows imported
Import terminated successfully without warnings.

SQL> host imp eric/eric@orcl file=t.dmp tables=t rows=n indexes=y constraints=y ignore=y

Import: Release 10.2.0.1.0 - Production on Thu Jan 8 13:14:05 2009

Copyright (c) 1982, 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

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing ERIC's objects into ERIC
. importing ERIC's objects into ERIC
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

这样就正常了，所以在表和索引分步导入时一定要注意了，特别是表上有主键的时候。

refer to : http://yangtingkun.itpub.net/post/468/280048