PROBLEM

Users querying SDO_Geometry data owned by a different user may encounter Oracle bugs 4716096 and 5031836

Last Published: April 25, 2020

Description

SDE users querying SDO_Geometry data owned by a different user may encounter Oracle bugs 4716096 and 5031836. This can cause the spatial index on the SDO_Geometry column to not be used.

For more information consult Oracle documentation on the following:

"5031836 - SECURE VIEW MERGING ON 10.2 PREVENTS DOMAIN INDEX USAGE"
"4716096 - DOMAIN INDEX NOT PERFORMED IN QUERY ON VIEW BY A DIFFERENT USER"

Cause

This issue is caused by changes that were made to view security in Oracle 10gR2.

Solution or Workaround

To resolve this problem, do one of the following:

  • Upgrade to Oracle 10.2.0.4.
  • Alter the database system to set the optimizer_secure_view_merging parameter to false.

    Code:
    alter system set optimizer_secure_view_merging = FALSE

  • Grant the MERGE ANY VIEW permission to the user who is selecting the data.

    Code:
    grant merge any view to <the users selecting the data>


Article ID: 000010620

Software:
  • Legacy Products

Receive notifications and find solutions for new or common issues

Get summarized answers and video solutions from our new AI chatbot.

Download the Esri Support App

Discover more on this topic

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options