ORA-04023 无法验证或授权对象 select * from owner_views；

Bug 16713938 : SELECT ON VIEW FAILS WITH ORA-04023 ON ADG FROM VIEW OWNER SCHEMA

BUG属性

类型	B - Defect	已在产品版本中修复
严重性	2 - Severe Loss of Service	产品版本	11.2.0.3
状态	96 - Closed, Duplicate Bug	平台	23 - Oracle Solaris on SPARC (64-bit)
创建时间	2013-4-24	平台版本	10
更新时间	2019-4-5	基本 Bug	12378705
数据库版本	11.2.0.3	影响平台	Generic
产品源	Oracle	与此 Bug 相关的知识, 补丁程序和 Bug

 相关产品

产品线	Oracle Database Products	系列	Oracle Database Suite
区域	Oracle Database	产品	5 - Oracle Database - Enterprise Edition

Hdr: 16713938 11.2.0.3 RDBMS 11.2.0.3 DATAGUARD_ACTV PRODID-5 PORTID-23 12378705
Abstract: SELECT ON VIEW FAILS WITH ORA-4023 ON ADG FROM VIEW OWNER SCHEMA
 
*** 04/24/13 01:54 am *** (ADD: Impact/Symptom->WRONG RESULT - INCO...)
*** 04/24/13 01:54 am ***
 
 
BUG TYPE CHOSEN
===============
Code
 
== SubComponent: Data Guard Active Physical Standby ==
======================================================
DETAILED PROBLEM DESCRIPTION
============================
We are getting  this error in physical standby instance only.
 
ORA-4023: Object select * from NACA.SL_BUSINESS_TYPE_URGE
ORA-4023: Object select * from NACA.KPI_GSS_V_NEW
ORA-4023: Object select * from NACAGBRNEW.SPRDESTREGIONS
 
But this error is not getting when we tried in running as a sys , only it
errored out in the views respective owners.
We are following temporary work around (alter system flush shared_pool) to
avoid this errors.
 
DIAGNOSTIC ANALYSIS
===================
Temporary work around (alter system flush shared_pool) is working for
avoiding errors so it seems to be a defect.
 
WORKAROUND?
===========
Yes
 
WORKAROUND INFORMATION
======================
SQL> alter system flush shared_pool;
 
TECHNICAL IMPACT
================
They are not able to execute queries on view on active data guard and need to
flush the shared pool on regular basis to avoid error which is impacting
database performance.
 
RELATED ISSUES (bugs, forums, RFAs)
===================================
Bug 10373013 could be relevant as as per rediscovery trace file contains
"kksfbc: entering reparse diagnosis mode"
 
HOW OFTEN DOES THE ISSUE REPRODUCE AT CUSTOMER SITE?
====================================================
Always
 
DOES THE ISSUE REPRODUCE INTERNALLY?
====================================
No
 
IS A TESTCASE AVAILABLE?
========================
No
 
Link to IPS Package:
====================
not available
 
*** 04/24/13 03:40 am *** (CHG: Sta->10 Asg->TSENFT)
*** 04/24/13 03:40 am ***
*** 06/05/13 09:44 pm *** (CHG: Sta->16)
*** 06/05/13 09:44 pm ***
*** 06/09/13 09:50 pm ***
*** 06/09/13 09:50 pm ***
*** 07/04/13 01:10 am *** (CHG: Base Bug-> NULL -> 12378705)
*** 07/04/13 01:10 am *** (CHG: Sta->96)
*** 07/04/13 01:10 am ***
*** 08/23/13 12:10 am ***
*** 08/29/13 01:52 am ***
*** 10/07/13 11:18 pm ***
*** 01/20/14 02:35 am ***
*** 05/01/14 08:33 am ***
*** 09/24/14 11:41 pm ***
*** 10/20/14 08:36 am ***
*** 10/30/14 09:02 am ***
*** 11/05/14 08:19 am ***
*** 12/01/14 06:37 am ***
*** 04/06/15 11:36 pm ***
*** 04/28/15 10:24 pm ***
*** 06/08/15 05:02 pm ***
*** 07/13/15 02:02 am ***
*** 08/17/15 09:18 am ***
*** 08/27/15 12:30 am ***
*** 10/08/15 01:06 am ***
*** 11/15/15 08:37 pm ***
*** 01/04/16 01:36 pm ***
*** 02/01/16 11:56 pm ***
*** 06/16/16 07:59 am ***
*** 08/09/16 09:35 pm ***
*** 09/23/16 11:13 am ***
*** 12/05/16 12:35 pm ***
*** 02/16/17 07:47 am ***
*** 02/27/17 05:05 pm ***
*** 12/22/17 05:53 pm ***
*** 08/10/18 10:53 pm ***
*** 04/05/19 09:53 am ***

目前无任何修复补丁

解决方案

重建报错视图，ADG再select时可正常返回结果集。

 

MOS链接

Bug 16713938 : SELECT ON VIEW FAILS WITH ORA-04023 ON ADG FROM VIEW OWNER SCHEMA