Catupgrd.sql Fails With 3113 Creating SYS.KU$_XMLSCHEMA_VIEW[ID 603714.1]

rongshiyuan發表於2012-09-05
SQLAIXMLView
10.2.0.4 Catupgrd.sql Fails With ORA-03113 Creating SYS.KU$_XMLSCHEMA_VIEW [ID 603714.1]

In this Document
Symptoms
Cause
Solution
References

Applies to:

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

Symptoms

-- Problem Statement:

Patching the production database from 10.2.0.3.to 10.2.0.4 after starting the database in upgrade mode.
While running the catupgrd.sql script, the upgrade fails reproducibly at the following statement.

SQL> create or replace force view sys.ku$_xmlschema_view of sys.ku$_xmlschema_t
2 with object identifier (schemaoid) as
3 select '1','0',
4 u.user#, u.name, extractvalue(VALUE(s), '/schema/@x:schemaURL',
5 'xmlns="http://www.w3.org/2001/XMLSchema" xmlns:x="http://xmlns.oracle.com/xdb"'),
6 s.sys_nc_oid$,
7 case when under_path(value(r), '/sys/schemas/PUBLIC') = 1
8 then 0 else 1 end,
9 s.getclobval(), -- unstripped
10 xdb.dbms_xdbutil_int.XMLSchemaStripUsername(XMLTYPE(s.getClobVal()),
11 u.name) -- stripped
12 from sys.user$ u, xdb.xdb$schema s, xdb.xdb$resource r
13 where extractvalue(VALUE(r), '/Resource/XMLRef') = ref(s)
14 and u.user# = sys_op_rawtonum(extractvalue(VALUE(r),'/Resource/OwnerID'))
15 AND (SYS_CONTEXT('USERENV','CURRENT_USERID') IN (u.user#, 0) OR
16 EXISTS ( SELECT * FROM session_roles
17 WHERE role='SELECT_CATALOG_ROLE' ))
18 /
create or replace force view sys.ku$_xmlschema_view of sys.ku$_xmlschema_t
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel


Also when running the relevant select separately, it fails with the same ORA-3113 error.



SQL> select a.name "OWNER",
2 b.name "NAME",
3 b.obj# "OBJ#"
4 from sys.user$ a,
5 sys.obj$ b
6 where b.name in ('XDB$NMSPC_ID', 'XDB$QNAME_ID', 'XDB$PATH_ID')
7 and a.user# = b.owner#;

OWNER NAME OBJ#
----- ------------ -----
XDB XDB$PATH_ID 41341
XDB XDB$QNAME_ID 41340
XDB XDB$NMSPC_ID 41339

Cause

The following event was set as a workaround for Bug 4684209 which is fixed in patch set 10.2.0.4

event='10195 trace name context forever, level 1'

Solution

-- To implement the solution, please execute the following steps::

1. Remove the following event from the parameter file as the workaround for Bug 4684209 is no longer needed with patch set 10.2.0.4

event='10195 trace name context forever, level 1'


2. Execute the 10.2.0.4 catupgrd.sql script. again.

References

BUG:4684209 - ORA-7445 [EVAOPN2] RUNNING A QUERY WITH ORDER BY AGAINST A VIEW
BUG:5161782 - ORA-3113 ON VIEW SYS.KU$_XMLSCHEMA_VIEW

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17252115/viewspace-742693/，如需轉載，請註明出處，否則將追究法律責任。

相關文章