Oracle 9.2.0.8 ODCIXXX 对象无效

韩3039

将Oracle 升级到9.2.0.8. 刚才查看无效对象，发现有几个ODCxx 开头的无效对象。 查看Oracle，说是9.2.0.8 以后的版本中，不知道的原因导致这些对象无效，这些对象在Oracle 10.2 以后的版本中已经删除掉了。 所以可以忽略这些无效对象。


SQL> select object_type,owner,object_name from all_objects where status='INVALID' and owner='SYS';

OBJECT_TYPE OWNER OBJECT_NAME
------------------ ------------------------------ ------------------------------
TYPE SYS ODCICOLINFO
TYPE SYS ODCIINDEXINFO
TYPE SYS ODCIINDEXINFO
TYPE SYS ODCIARGDESC
TYPE SYS ODCICOST

尝试修复对象：

SQL> ALTER TYPE SYS.ODCICOST COMPILE;
ALTER TYPE SYS.ODCICOST COMPILE
*
第 1 行出现错误:
ORA-02311: 无法使用 COMPILE 选项改变具有类型或表相关性的有效类型

ORA-02311:	cannot alter with COMPILE option a valid type with type or table dependents
Cause:	An attempt was made to ALTER with COMPILE option a type that is valid and has type or table dependents.
Action:	No need to perform this operation.

Oracle 的解释：

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.8 and later[Release: 9.2 and later ]
Information in this document applies to any platform.

Symptoms

The following invalid objects have been found in Oracle 9.2.0.8.0 database:

SQL> select * from dba_objects where status!='VALID' and owner='SYS';

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- ------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- --------- ------------------- ------- - - -
SYS
ODCIARGDESC
$VSN_1 3379 TYPE
12-MAY-02 12-MAY-02 2002-05-12:16:18:04 INVALID N N N

SYS
ODCICOLINFO
$VSN_1 3362 TYPE
12-MAY-02 12-MAY-02 2002-05-12:16:18:02 INVALID N N N

SYS
ODCICOST
$VSN_1 3377 TYPE
12-MAY-02 12-MAY-02 2002-05-12:16:18:03 INVALID N N N

SYS
ODCIINDEXINFO
$VSN_1 3366 TYPE
12-MAY-02 12-MAY-02 2002-05-12:16:18:02 INVALID N N N

SYS
ODCIINDEXINFO
$VSN_2 3367 TYPE
12-MAY-02 12-MAY-02 2002-05-12:16:18:02 INVALID N N N

Cause

It is unknown what has turned the objects invalid.

Solution

The objects in question will be dropped during upgrade to Oracle 10.2.x / 11.1.x / 11.2.x due to fact that the following script is run as part of upgrade: ?/rdbms/admin/c090200.sql:

...
Rem Drop these types so that they will be recreated (no longer evolved)
DROP TYPE ODCIIndexInfo FORCE;
DROP TYPE ODCICost FORCE;
DROP TYPE ODCIArgDesc FORCE;
DROP TYPE ODCIEnv FORCE;

------------------------------------------------------------------------------
Blog： http://blog.csdn.net/tianlesoftware
网上资源： http://tianlesoftware.download.csdn.net
相关视频：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
DBA1 群：62697716(满); DBA2 群：62697977(满)
DBA3 群：62697850 DBA 超级群：63306533;
聊天 群：40132017
--加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请