History | Log In     View a printable version of the current page.  
   HOME       BROWSE PROJECT       FIND ISSUES   
    QUICK SEARCH:  
Issue Details (XML | Word | Printable)

Key: QA-40
Type: Oracle - Administration Oracle - Administration
Status: Closed Closed
Resolution: Answered
Priority: Major Major
Assignee: ubTools Support
Reporter: ubTools Support
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Questions & Answers

"Oracle Database Server" status is INVALID after applying 10.2.0.4 PatchSet.

Created: 15/Jun/08 06:06 PM   Updated: 15/Jun/08 06:34 PM
Fix Version/s: None

Product Version: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
Operating System: IBM-AIX


 Description  « Hide
After applying 10.2.0.4.0 PatchSet into 10.2.0.3.0, catupgrd.sql logs shows the following: 
...
SQL> CREATE OR REPLACE PACKAGE BODY dbms_sqlpa wrapped
  2  a000000
  3  1
  4  abcd
  5  abcd
  6  abcd
...
Warning: Package Body created with compilation errors.

SQL> show errors;
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
SQL> 
...
Component                                Status         Version  HH:MM:SS
Oracle Database Server                  INVALID      10.2.0.4.0  00:09:22
JServer JAVA Virtual Machine              VALID      10.2.0.4.0  00:02:43
Oracle XDK                                VALID      10.2.0.4.0  00:00:29
Oracle Database Java Packages             VALID      10.2.0.4.0  00:00:14
Oracle Text                               VALID      10.2.0.4.0  00:00:21
Oracle XML Database                       VALID      10.2.0.4.0  00:02:02
Oracle Workspace Manager                  VALID      10.2.0.4.3  00:00:43
Oracle Data Mining                        VALID      10.2.0.4.0  00:00:20
OLAP Analytic Workspace                   VALID      10.2.0.4.0  00:00:16
OLAP Catalog                              VALID      10.2.0.4.0  00:00:55
Oracle OLAP API                           VALID      10.2.0.4.0  00:00:43
Oracle interMedia                         VALID      10.2.0.4.0  00:02:24
Spatial                                   VALID      10.2.0.4.0  00:01:34
Oracle Ultra Search                       VALID      10.2.0.4.0  00:00:22
Oracle Expression Filter                  VALID      10.2.0.4.0  00:00:09
Oracle Enterprise Manager                 VALID      10.2.0.4.0  00:01:36
Oracle Rule Manager                       VALID      10.2.0.4.0  00:00:08
.


 All   Comments   Change History      Sort Order: Ascending order - Click to sort in descending order
[ Permlink | « Hide ]
ubTools Support - 15/Jun/08 06:32 PM
Compiling DBMS_SQLPA causes the problem. To find the object including OTHER_XML column, ERRORSTACK trace for ORA-904 would be useful. But, since it's a known column of PLAN_TABLE, it's not required while diagnosing the problem.

There were both SYS.PLAN_TABLE as a table and PUBLIC.PLAN_TABLE as a public synonym in the database:

SQL> select owner,object_name,object_type from dba_objects where owner in ('SYS','PUBLIC') and upper(object_name)  like 'PLAN_TABLE%';

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-------------------
PUBLIC
PLAN_TABLE
SYNONYM

SYS
PLAN_TABLE
TABLE

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-------------------

SYS
PLAN_TABLE$
TABLE


SQL> select TABLE_OWNER,TABLE_NAME from dba_synonyms where OWNER='PUBLIC' and SYNONYM_NAME='PLAN_TABLE';

TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------
SYS                            PLAN_TABLE$

SQL>

But, not all columns of SYS.PLAN_TABLE table and PUBLIC.PLAN_TABLE synonym are same:

SQL> desc sys.plan_table
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 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(38)
 OBJECT_TYPE                                        VARCHAR2(30)
 OPTIMIZER                                          VARCHAR2(255)
 SEARCH_COLUMNS                                     NUMBER
 ID                                                 NUMBER(38)
 PARENT_ID                                          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

SQL>

SQL> desc sys.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)

SQL>

Since table access takes precedence on synonym access, SYS.PLAN_TABLE table was used. But, this table doesn't have a column named OTHER_XML, which caused the problem.

After dropping SYS.PLAN_TABLE table, PUBLIC.PLAN_TABLE synonym used:

SQL> drop table sys.plan_table;

Table dropped.

SQL>



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)

SQL>

Applying PatchSet did not give INVALID status:

Component                                Status         Version  HH:MM:SS
Oracle Database Server                    VALID      10.2.0.4.0  00:09:20
JServer JAVA Virtual Machine              VALID      10.2.0.4.0  00:02:56
Oracle XDK                                VALID      10.2.0.4.0  00:00:28
Oracle Database Java Packages             VALID      10.2.0.4.0  00:00:14
Oracle Text                               VALID      10.2.0.4.0  00:00:22
Oracle XML Database                       VALID      10.2.0.4.0  00:02:05
Oracle Workspace Manager                  VALID      10.2.0.4.3  00:00:45
Oracle Data Mining                        VALID      10.2.0.4.0  00:00:21
OLAP Analytic Workspace                   VALID      10.2.0.4.0  00:00:16
OLAP Catalog                              VALID      10.2.0.4.0  00:00:55
Oracle OLAP API                           VALID      10.2.0.4.0  00:00:41
Oracle interMedia                         VALID      10.2.0.4.0  00:02:24
Spatial                                   VALID      10.2.0.4.0  00:01:37
Oracle Ultra Search                       VALID      10.2.0.4.0  00:00:22
Oracle Expression Filter                  VALID      10.2.0.4.0  00:00:09
Oracle Enterprise Manager                 VALID      10.2.0.4.0  00:01:37
Oracle Rule Manager                       VALID      10.2.0.4.0  00:00:08
.

[ Permlink | « Hide ]
ubTools Support - 15/Jun/08 06:34 PM
  • Drop SYS.PLAN_TABLE table.
  • Install PatchSet.
Powered by Atlassian JIRA the Professional Issue Tracker. (Standard Edition, Version: 3.12.3-#302) - Bug/feature request - Atlassian news - Contact Administrators