修复oracle组件catproc-onlaoc
数据库版本：SQL> select * from v$version;
Oracle Database 10g Enterprise Edition>
PL/SQL> 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
------------------------------------------------------------------------------------------------------------------------------------------------------
VERSION STATUS MODIFIED NAMESPACE CONTROL
------------------------------ ---------------------- ----------------------- ------------------------------ ------------------------------
SCHEMA PROCEDURE STARTUPPARENT_ID
------------------------------ ------------------------------------------------------------- -------- ------------------------------
OTHER_SCHEMAS
------------------------------------------------------------------------------------------------------------------------------------------------------
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
2start_time date;
3end_time date;
4object_name varchar(100);
5object_id char(10);
6begin
7SELECT date_loading, date_loaded into start_time, end_time FROM registry$ WHERE
8cid = 'CATPROC';
9SELECT 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 ROWNUMselect owner,object_id,object_name,OBJECT_TYPE from dba_objects where object_id=9662;
OWNER OBJECT_ID
------------------------------ ----------
OBJECT_NAME OBJECT_TYPE
-------------------------------------------------------------------------------------------------------------------------------- -------------------
SYS 9662
DBMS_SQLPA
SQL>>
Warning: Package Body> 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> 发现编译该包的时候报错，出现在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') */
> 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
------------------------------ ------------------------------ ------------------------------ ---------- -------------- -------------------
CREATED LAST_DDL_TIM TIMESTAMP STATUST 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
------------------------------------------------------------------------------------------------------------------------------------------------------
VERSION STATUS MODIFIED NAMESPACE CONTROL
------------------------------ ---------------------- ----------------------- ------------------------------ ------------------------------
SCHEMA PROCEDURE STARTUPPARENT_ID
------------------------------ ------------------------------------------------------------- -------- ------------------------------
OTHER_SCHEMAS
------------------------------------------------------------------------------------------------------------------------------------------------------
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组建状态已经被修复
页:
[1]