IMP-00060:type does not exist or has different identifier

今天在做一个数据库迁移的时候,在imp的时候出现了一个奇怪的报错:

……
. . importing table         "APPLY_SERV_BAK070713"       1542 rows imported
. . importing table         "APPLY_SERV_BAK070724"       1546 rows imported
. . importing table                "APPLY_SMS_MSG"         61 rows imported
. . importing table            "APPLY_SMS_MSG_HIS"      30274 rows imported
IMP-00060: Warning: Skipping table "APP_USER"."AQROUTERLOG_QT" because object type "SYS"."AQ$_JMS_USERPROPARRAY"  does not exist or has different identifier
. . importing table                     "ARCHIVES"          0 rows imported
. . importing table                 "ARCHIVESTYPE"          0 rows imported
……

当时觉得非常奇怪,查询了”SYS”.”AQ$_JMS_USERPROPARRAY” 这个object是存在的,所以提示中说的不存在应该是不可能,而且exp的时候,都是成功导出的:

.......
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

当时也没仔细看提示还说了“or has different identifier”,就重新导入了一次,但是还是报了同样的错误。

再次测试先建立其中的表 “APP_USER”.”AQROUTERLOG_QT”,再导入数据,仍旧发现还是没有数据导入:

[oracle@my_testdb01 data]$ sqlplus app_user/app_pwd

SQL*Plus: Release 9.2.0.6.0 - Production on Fri Aug 10 14:10:46 2007

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


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

SQL> show user                          
USER is "APP_USER"
SQL> select count(*) from AQRouterLog_QT;

  COUNT(*)
----------
         0

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
[oracle@my_testdb01 data]$ imp app_user/app_user file=tb_AQROUTERLOG_QT.dmp fromuser=app_user touser=app_user ignore=y

Import: Release 9.2.0.6.0 - Production on Fri Aug 10 14:11:19 2007

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


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

Export file created by EXPORT:V09.02.00 via direct path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
IMP-00060: Warning: Skipping table "APP_USER"."AQROUTERLOG_QT" because object type "SYS"."AQ$_JMS_USERPROPARRAY"  does not exist or has different identifier
Import terminated successfully with warnings.
[oracle@my_testdb01 data]$ 
[oracle@my_testdb01 data]$ sqlplus app_user/app_pwd

SQL*Plus: Release 9.2.0.6.0 - Production on Fri Aug 10 14:12:07 2007

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


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

SQL> select count(*) from AQRouterLog_QT;

  COUNT(*)
----------
         0

SQL> exit

怎么办?!!

这个时候上网查了相关的资料,才发现无法导入是因为原数据库和目标数据”SYS”.”AQ$_JMS_USERPROPARRAY”这个TYPE的OID两边不一致,可以采用的解决方法有2种:
(1)手工将目标数据库的这个type的oid改成和原数据库一致的,这样在imp的时候,检验时会检验到两边oid一致,能够导入。但是这个方法适合type是自己写的,不是数据库的数据字典里面的。像我刚刚这个的报错,试图手工修改就报错了:

SQL> CREATE OR REPLACE TYPE "SYS"."AQ$_JMS_USERPROPARRAY" oid '1E5616FAE4A68D31E040C10A01006348'as varray(100) of aq$_jms_userproparray
  2  /
CREATE OR REPLACE TYPE "SYS"."AQ$_JMS_USERPROPARRAY" oid '1E5616FAE4A68D31E040C10A01006348'as varray(100) of aq$_jms_userproparray
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents

SQL> select referenced_name,referenced_type from dba_dependencies
  2  where name='AQ$_JMS_USERPROPARRAY';

REFERENCED_NAME      REFERENCED_TYPE
-------------------- ------------------------------
STANDARD             PACKAGE
AQ$_JMS_USERPROPERTY TYPE

报错有相关的依赖对象,顺着依赖的对象一路查下去,就到了STANDARD 这个package了,这个包是数据库里面的包,粗粗检查了一下,没有发现可以有修改oid的地方,也就不去动了。

(2)另一种的解决方式是在imp的时候使用toid_novalidate参数。使用的格式是:

imp ......  TOID_NOVALIDATE=(owner.type1,owner.type2,owner.type3...)

因此在上面的情况,我们可以使用imp语句为:
imp app_user/app_user file=tb_AQROUTERLOG_QT.dmp fromuser=app_user touser=app_user ignore=y TOID_NOVALIDATE=(SYS.AQ\$_JMS_USERPROPARRAY)

在这里,我们要特别注意一个细节问题,在win下,我们可以使用TOID_NOVALIDATE=(SYS.AQ$_JMS_USERPROPARRAY),但是在unix下,$会被解释成变量的提示符,因此我们必须使用转义字符“\”,写成TOID_NOVALIDATE=(SYS.AQ\$_JMS_USERPROPARRAY)!(之前由于没注意到这个问题,害我捣鼓了一下午 -_-||)。

####### 没用转义字符 \,从报错信息我们看到TOID_NOVALIDATE是AQ,不是AQ$_JMS_USERPROPARRAY
[oracle@my_testdb01 data]$ imp app_user/app_user file=tb_AQROUTERLOG_QT.dmp fromuser=app_user touser=app_user ignore=y TOID_NOVALIDATE=(SYS.AQ$_JMS_USERPROPARRAY)

Import: Release 9.2.0.6.0 - Production on Fri Aug 10 14:14:24 2007

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


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

Export file created by EXPORT:V09.02.00 via direct path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
IMP-00060: Warning: Skipping table "APP_USER"."AQROUTERLOG_QT" because object type "SYS"."AQ$_JMS_USERPROPARRAY"  does not exist or has different identifier
IMP-00086: TOID "AQ" not found in export file
Import terminated successfully with warnings.
[oracle@my_testdb01 data]$ 

###### 使用转义字符,原来的报错不再出现,但是又出现一个需要novalidate的type
[oracle@my_testdb01 data]$ imp app_user/app_user file=tb_AQROUTERLOG_QT.dmp fromuser=app_user touser=app_user ignore=y TOID_NOVALIDATE=(SYS.AQ\$_JMS_USERPROPARRAY)

Import: Release 9.2.0.6.0 - Production on Fri Aug 10 14:21:15 2007

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


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

Export file created by EXPORT:V09.02.00 via direct path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. . skipping TOID validation on type SYS.AQ$_JMS_USERPROPARRAY
IMP-00060: Warning: Skipping table "APP_USER"."AQROUTERLOG_QT" because object type "SYS"."AQ$_JMS_MESSAGE"  does not exist or has different identifier
Import terminated successfully with warnings.
[oracle@my_testdb01 data]$ 
[oracle@my_testdb01 data]$ 

###### 为2个type添加novalidate后,不再报错
[oracle@my_testdb01 data]$ imp app_user/app_user file=tb_AQROUTERLOG_QT.dmp fromuser=app_user touser=app_user ignore=y TOID_NOVALIDATE=(SYS.AQ\$_JMS_USERPROPARRAY,sys.AQ\$_JMS_MESSAGE)

Import: Release 9.2.0.6.0 - Production on Fri Aug 10 14:22:16 2007

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


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

Export file created by EXPORT:V09.02.00 via direct path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. . skipping TOID validation on type SYS.AQ$_JMS_USERPROPARRAY
. . skipping TOID validation on type SYS.AQ$_JMS_MESSAGE
. . importing table               "AQROUTERLOG_QT"        113 rows imported
Import terminated successfully without warnings.
[oracle@my_testdb01 data]$ 

至此,我们imp成功!

相关文章

2条评论

发表评论

电子邮件地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据