ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 分步导入可能会遇到的问题

分步导入可能会遇到的问题

原创 Linux操作系统 作者:yangtingkun 时间:2007-04-14 00:00:00 0 删除 编辑

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


看一个简单的例子:

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-69239/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10487607