Is This Content Helpful?
We're glad to know this article was helpful.
In some situations, ArcGIS is unable to connect to Oracle after installing certain releases of Oracle or installing certain Oracle Critical Patch Updates (CPUs). ArcGIS connections may hang, cause an error, or crash when attempting to establish a connection.
This behavior is encountered in Oracle version 18.104.22.168, and the first CPU to report this behavior was the October 2014 patch. This bug applies to all ArcGIS releases connecting to an Oracle instance with the applied Oracle permission change. This bug also applies to ArcGIS clients connecting to an Oracle instance with or without a geodatabase, and/or using ArcSDE Application Server Services.
Oracle’s dbms_utility package’s subprogram 'get_parameter_value' returns the following error:
"ORA-01031: insufficient privileges."
ArcGIS software calls the subprogram when creating a connection to the Oracle database to obtain the database parameter 'open_cursors' value. The value is required to ensure the ArcGIS session working with the Oracle instance does not exceed the maximum number of cursors available within the session.
ArcGIS holds cursors open for frequently executed SQL statements to improve performance and scalability. The impact of this issue, introduced after installing the Critical Patch Update, is that it prevents ArcGIS from successfully establishing a connection.
For additional information on Oracle's Critical Patch Update Advisory, please visit Oracle's website.
As an alternative workaround, the system administrator can grant the SELECT_CATALOG_ROLE to each user that accesses the Oracle instance with ArcGIS.
Esri contacted Oracle Technical Support (Oracle SR 3-9775575171) and verified that there was a permission change on the PUBLIC role that was rolled into some of their releases and CPUs. Based on Oracle's instructions, the recommendation to ensure ArcGIS applications are able to successfully connect to the Oracle instance, is to explicitly grant SELECT privileges on the v$parameter view to each user or the public role:
• Connect in SQL*Plus as the SYS user and grant SELECT on the v_$parameter table to each named user or the PUBLIC role.
SQL> GRANT SELECT ON v_$parameter TO PUBLIC;