IMP-00060:type does not exist or has different identifier
来源:互联网 发布:c 并口编程 编辑:程序博客网 时间:2024/05/17 03:46
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.
这个时候上网查了相关的资料,才发现无法导入是因为原数据库和目标数据”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成功!
原文来自:http://www.oracleblog.org/working-case/imp-00060type-does-not-exist-or-has-different-identifier/
- IMP-00060:type does not exist or has different identifier
- Androidmanifest xml does not exist or has incorrect root tag
- The type or namespace name 'Windows' does not exist in the class or namespace 'System'
- Android studio 出现错误Androidmanifest xml does not exist or has incorrect root tag
- Error:The type or namespace name 'Office' does not exist in the namespace 'Microsoft' 解决方法
- The type or namespace name 'Service' does not exist 异常处理
- 关于Service 'XXXX' of type 'MapServer' does not exist or is inaccessible问题的解决
- ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type
- 解决The type or namespace name \'XXXX\' does not exist in the namespace \'XXXXXXXXX\' 的错误
- Service 'china' of type 'MapServer' does not exist or is inaccessible
- oracle ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type
- CS0234The type or namespace name 'Mvc' does not exist in the namespace 'Microsoft.AspNet' (are you
- The type or namespace name 'Zhaopin' does not exist in the namespace 'LMSoft' 问题的解决
- CS0234: The type or namespace name 'Linq' does not exist in the namespace 'System.Data'
- table or view does not exist -- oracle
- table or view does not exist
- The query does not start with a valid identifier, has to be either SELECT, UPDATE or DELETE FROM
- RPC Fault faultString="[MessagingError message='Destination 'data-push' either does not exist or the destination has no channels
- JAVA设计模式十八:--Facade(外观模式或门面模式)
- Android图形图像
- 如何在jsp里面定义方法
- uva 10129 - Play on Words
- 图片合成
- IMP-00060:type does not exist or has different identifier
- dotConnect for Oracle使用教程:Oracle对象导航的使用
- 求两个等长有序序列的中位数
- Visual Studio 2012 Ultimate RTM 体验(附下载地址和KEY)
- 对java synchronized 关键字的理解
- 用Delphi7 Post 请求到Tomcat,得到Response,中文OK
- 黑马程序员——C#面向对象小知识点
- 查看select
- /bin/bash^M: bad interpreter: No such file or directory