'Oracle Database Server invalid' after

Manually upgraded a – database to The output of ‘catupgrd.sql’ shows me the result in the end:

Oracle Database Server INVALID 00:15:57   ,   while the rest is VALID…

Other components:

JServer JAVA Virtual Machine VALID 00:00:00
Oracle XDK VALID 00:00:00
Oracle Database Java Packages VALID 00:00:00
Oracle Text VALID 00:00:00
Oracle XML Database VALID 00:00:00
Oracle Workspace Manager VALID 00:00:30
Oracle interMedia VALID 00:00:00
Spatial VALID 00:00:00

The logging showed that the package dbms_sqlpa was created with compilation errors.

http://www.ubtools.com/jira/browse/QA-40  put me on the right track:

SQL> show errors;
-------- -----------------------------------------------------------------
113/5    PL/SQL: SQL Statement ignored
118/44   PL/SQL: ORA-00904: "OTHER_XML": invalid identifier
This package needs the table PLAN_TABLE with a column OTHER_XML, and there's a PLAN_TABLE in user SYS which hasn't.
The synonym appears to point to the right table:

Executed the following and:
descr sys.plan_table (table in SYS).
descr sys.plan_table$ (synonym)


SQL> drop table sys.plan_table;

Table dropped.

SQL> desc plan_table
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 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(38)
 OBJECT_TYPE                                        VARCHAR2(30)
 OPTIMIZER                                          VARCHAR2(255)
 SEARCH_COLUMNS                                     NUMBER
 ID                                                 NUMBER(38)
 PARENT_ID                                          NUMBER(38)
 DEPTH                                              NUMBER(38)
 POSITION                                           NUMBER(38)
 COST                                               NUMBER(38)
 CARDINALITY                                        NUMBER(38)
 BYTES                                              NUMBER(38)
 OTHER_TAG                                          VARCHAR2(255)
 PARTITION_START                                    VARCHAR2(255)
 PARTITION_STOP                                     VARCHAR2(255)
 PARTITION_ID                                       NUMBER(38)
 OTHER                                              LONG
 OTHER_XML                                          CLOB
 DISTRIBUTION                                       VARCHAR2(30)
 CPU_COST                                           NUMBER(38)
 IO_COST                                            NUMBER(38)
 TEMP_SPACE                                         NUMBER(38)
 ACCESS_PREDICATES                                  VARCHAR2(4000)
 FILTER_PREDICATES                                  VARCHAR2(4000)
 PROJECTION                                         VARCHAR2(4000)
 TIME                                               NUMBER(38)
 QBLOCK_NAME                                        VARCHAR2(30)

Ran 'catupgrd.sql' again when I have started the database with upgrade optiom, and all was VALID.
By | 2008-12-31T08:33:19+00:00 December 31st, 2008|Categories: Database|0 Comments

About the Author:

Leave A Comment