数据库版本：
SQL> select * from v$version;
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for HPUX: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
组件CATPROC处于invalid状态
SQL> select * from dba_registry;
COMP_ID
------------------------------
COMP_NAME
------------------------------------------------------------------------------------------------------------------------------------[02:19:29]------------------
VERSION STATUS MODIFIED NAMESPACE CONTROL
------------------------------ ---------------------- ----------------------- ------------------------------ -----------------------[02:19:29]-------
SCHEMA PROCEDURE STARTUP PARENT_ID
------------------------------ ------------------------------------------------------------- -------- ------------------------------
OTHER_SCHEMAS
------------------------------------------------------------------------------------------------------------------------------------[02:19:29]------------------
ODM
Oracle Data Mining
10.2.0.4.0 VALID 12-AUG-2013 03:46:46 SERVER SYS
DMSYS VALIDATE_ODM
CATALOG
Oracle Database Catalog Views
10.2.0.4.0 VALID 12-AUG-2013 03:46:44 SERVER SYS
SYS DBMS_REGISTRY_SYS.VALIDATE_CATALOG
CATPROC
Oracle Database Packages and Types
10.2.0.4.0 INVALID 12-AUG-2013 03:46:44 SERVER SYS
SYS DBMS_REGISTRY_SYS.VALIDATE_CATPROC
DBSNMP,OUTLN,SYSTEM
查找该组建相关的包：
SQL> set serveroutput on;
SQL> declare
2 start_time date;
3 end_time date;
4 object_name varchar(100);
5 object_id char(10);
6 begin
7 SELECT date_loading, date_loaded into start_time, end_time FROM registry$ WHERE
8 cid = 'CATPROC';
9 SELECT obj#,name into object_id,object_name
10 FROM obj$
11 WHERE status > 1 AND
12 (ctime BETWEEN start_time AND end_time OR
13 mtime BETWEEN start_time AND end_time OR
14 stime BETWEEN start_time AND end_time) AND
15 ROWNUM <=1;
16 dbms_output.put_line('Please compile Invalid object '||object_name||'
17 Object_id '||object_id );
18 EXCEPTION
19 WHEN NO_DATA_FOUND THEN
20 dbms_output.put_line('CATPROC can be validated now' );
21 end;
22 /
Please compile Invalid object DBMS_SQLPA
Object_id 9662
找到无效对象：
SQL> select owner,object_id,object_name,OBJECT_TYPE from dba_objects where object_id=9662;
OWNER OBJECT_ID
------------------------------ ----------
OBJECT_NAME OBJ[02:29:29]ECT_TYPE
-------------------------------------------------------------------------------------------------------------------------------- ---[02:29:29]----------------
SYS 9662
DBMS_SQLPA
SQL> alter package dbms_sqlpa compile body;;
Warning: Package Body altered with compilation errors.
SQL> show error
Errors for PACKAGE BODY DBMS_SQLPA:
LINE/COL ERROR
-------- --------------------------------------------------
113/5 PL/SQL: SQL Statement ignored
118/44 PL/SQL: ORA-00904: "OTHER_XML": invalid identifier
发现编译该包的时候报错，出现在113行；由于系统包代码被oracle加密，可以使用halledba的工具fyunwrap解密；
查看该包113行内容为：
INSERT INTO PLAN_TABLE
(ID, POSITION, DEPTH, OPERATION, OPTIONS, OBJECT_NAME,
CARDINALITY, BYTES, TEMP_SPACE, COST, IO_COST, CPU_COST,
TIME, PARTITION_START, PARTITION_STOP, OBJECT_NODE, OTHER_TAG,
DISTRIBUTION, PROJECTION, ACCESS_PREDICATES, FILTER_PREDICATES,
QBLOCK_NAME, OBJECT_ALIAS, OTHER_XML)
(SELECT /*+ opt_param('parallel_execution_enabled', 'false') */
ID, POSITION, DEPTH, OPERATION, OPTIONS, OBJECT_NAME,
CARDINALITY, BYTES, TEMP_SPACE, COST, IO_COST, CPU_COST,
TIME, PARTITION_START, PARTITION_STOP, OBJECT_NODE, OTHER_TAG,
DISTRIBUTION, PROJECTION, ACCESS_PREDICATES, FILTER_PREDICATES,
QBLOCK_NAME, OBJECT_ALIAS, OTHER_XML
FROM TABLE(SQL_PLAN));
检查plan_table:
SQL> select * from dba_objects where object_name='PLAN_TABLE';
OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ------------------------------ ------------------------------ ---------- -------------- -------------[02:39:19]------
CREATED LAST_DDL_TIM TIMESTAMP STATUS T G S
------------ ------------ ------------------- ------- - - -
SYS PLAN_TABLE 236182 236182 TABLE
03-JAN-11 03-JAN-11 2011-01-03:11:25:24 VALID N N N
PUBLIC PLAN_TABLE 7700 SYNONYM
19-APR-09 19-APR-09 2009-04-19:22:11:18 VALID N N N
SQL> @get_ddl
CREATE TABLE "SYS"."PLAN_TABLE"
( "STATEMENT_ID" VARCHAR2(30),
"TIMESTAMP" DATE,
"REMARKS" VARCHAR2(80),
"OPERATION" VARCHAR2(30),
"OPTIONS" VARCHAR2(255),
"OBJECT_NODE" VARCHAR2(128),
"OBJECT_OWNER" VARCHAR2(30),
"OBJECT_NAME" VARCHAR2(30),
"OBJECT_INSTANCE" NUMBER(*,0),
"OBJECT_TYPE" VARCHAR2(30),
"OPTIMIZER" VARCHAR2(255),
"SEARCH_COLUMNS" NUMBER,
"ID" NUMBER(*,0),
"PARENT_ID" NUMBER(*,0),
"POSITION" NUMBER(*,0),
"COST" NUMBER(*,0),
"CARDINALITY" NUMBER(*,0),
"BYTES" NUMBER(*,0),
"OTHER_TAG" VARCHAR2(255),
"PARTITION_START" VARCHAR2(255),
"PARTITION_STOP" VARCHAR2(255),
"PARTITION_ID" NUMBER(*,0),
"OTHER" LONG,
"DISTRIBUTION" VARCHAR2(30),
"CPU_COST" NUMBER(*,0),
"IO_COST" NUMBER(*,0),
"TEMP_SPACE" NUMBER(*,0)
)
另外一个正常库的plan_table表结构为
CREATE TABLE "SYS"."PLAN_TABLE"
( "STATEMENT_ID" VARCHAR2(30),
"PLAN_ID" NUMBER,
"TIMESTAMP" DATE,
"REMARKS" VARCHAR2(4000),
"OPERATION" VARCHAR2(30),
"OPTIONS" VARCHAR2(255),
"OBJECT_NODE" VARCHAR2(128),
"OBJECT_OWNER" VARCHAR2(30),
"OBJECT_NAME" VARCHAR2(30),
"OBJECT_ALIAS" VARCHAR2(65),
"OBJECT_INSTANCE" NUMBER(*,0),
"OBJECT_TYPE" VARCHAR2(30),
"OPTIMIZER" VARCHAR2(255),
"SEARCH_COLUMNS" NUMBER,
"ID" NUMBER(*,0),
"PARENT_ID" NUMBER(*,0),
"DEPTH" NUMBER(*,0),
"POSITION" NUMBER(*,0),
"COST" NUMBER(*,0),
"CARDINALITY" NUMBER(*,0),
"BYTES" NUMBER(*,0),
"OTHER_TAG" VARCHAR2(255),
"PARTITION_START" VARCHAR2(255),
"PARTITION_STOP" VARCHAR2(255),
"PARTITION_ID" NUMBER(*,0),
"OTHER" LONG,
"DISTRIBUTION" VARCHAR2(30),
"CPU_COST" NUMBER(*,0),
"IO_COST" NUMBER(*,0),
"TEMP_SPACE" NUMBER(*,0),
"ACCESS_PREDICATES" VARCHAR2(4000),
"FILTER_PREDICATES" VARCHAR2(4000),
"PROJECTION" VARCHAR2(4000),
"TIME" NUMBER(*,0),
"QBLOCK_NAME" VARCHAR2(30),
"OTHER_XML" CLOB
发现明显表结构不一致；
对plan_table表重建或者rename；
重新运行编译脚本
@?/rdbms/admin/utlrp.sql
SQL> execute DBMS_REGISTRY_SYS.VALIDATE_CATPROC;
PL/SQL procedure successfully completed.
SQL> select * from dba_registry;
COMP_ID
------------------------------
COMP_NAME
------------------------------------------------------------------------------------------------------------------------------------[02:52:40]------------------
VERSION STATUS MODIFIED NAMESPACE CONTROL
------------------------------ ---------------------- ----------------------- ------------------------------ -----------------------[02:52:40]-------
SCHEMA PROCEDURE STARTUP PARENT_ID
------------------------------ ------------------------------------------------------------- -------- ------------------------------
OTHER_SCHEMAS
------------------------------------------------------------------------------------------------------------------------------------[02:52:40]------------------
CONTEXT
Oracle Text
10.2.0.4.0 VALID 13-AUG-2013 02:51:32 SERVER SYS
CTXSYS VALIDATE_CONTEXT
EXF
Oracle Expression Filter
10.2.0.4.0 VALID 13-AUG-2013 02:51:31 SERVER SYS
EXFSYS VALIDATE_EXF
RUL
Oracle Rules Manager
10.2.0.4.0 VALID 13-AUG-2013 02:51:32 SERVER SYS
EXFSYS VALIDATE_RUL
OWM
Oracle Workspace Manager
10.2.0.4.3 VALID 13-AUG-2013 02:51:31 SERVER SYS
WMSYS VALIDATE_OWM
ODM
Oracle Data Mining
10.2.0.4.0 VALID 13-AUG-2013 02:51:32 SERVER SYS
DMSYS VALIDATE_ODM
CATALOG
Oracle Database Catalog Views
10.2.0.4.0 VALID 13-AUG-2013 02:51:31 SERVER SYS
SYS DBMS_REGISTRY_SYS.VALIDATE_CATALOG
CATPROC
Oracle Database Packages and Types
10.2.0.4.0 VALID 13-AUG-2013 02:52:25 SERVER SYS
SYS DBMS_REGISTRY_SYS.VALIDATE_CATPROC
DBSNMP,OUTLN,SYSTEM
再次检查发现catproc组建状态已经被修复
转载于:https://blog.51cto.com/csdw81/1272256
487
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
