English

How To: Configure the geodatabase open_cursors server_config parameter for Oracle

Summary

Because of the change in Oracle's Critical Patch Update - October 2014 release, ArcGIS is unable to obtain the value for an Oracle instance's open_cursors parameter unless the user connecting has been granted privileges to access the v$parameter view.

The geodatabase requires knowing the value for the open_cursors parameter to avoid encountering Oracle's error "ORA-01000: maximum open cursors exceeded". To improve performance and scalability when working with Oracle, an Oracle recommended best practice is to keep cursors which are frequently executed 'open' to avoid contention within Oracle's system global area (SGA).

For example, ArcGIS keeps several cursors open within the session; spatial queries, cursors used to perform frequent editing operations, cursors used to access the geodatabase data dictionary, etc.

With the changes in Oracle's Critical Patch Update, ArcGIS encounters an error executing the dbms_utility.get_parameter_value when establishing a connection for the purpose of obtaining the open_cursors value. When an error is encountered, ArcGIS checks if a parameter named open_cursors exists in the geodatabase's server_config table. If the parameter is not present, ArcGIS sets the default the value to 300.

For ArcGIS applications connecting to an Oracle instance without a geodatabase, the connecting session still attempts to execute the dbms_utility.get_paramter_value and if the session encounters an error, ArcGIS automatically sets the value for open_cursors to 300.

In summary, when an ArcGIS session connects to an Oracle instance, ArcGIS executes the dbms_utility.get_parameter_value subprogram, if an error is encountered (because the user connecting lacks privileges) ArcGIS checks if the open_cursors parameter exists in the geodatabase server_config table and when not present, automatically sets the default to a value of 300.

The following instructions provide guidance on how to configure the geodatabase open_cursors server_config parameter for Oracle.

Procedure

As the geodatabase SDE administrative user with privileges to access the v$parameter view, connect in SQL*Plus and execute the following procedure.

  1. If using ArcGIS 10.2.2 or previous release, apply the ArcGIS 'Oracle Critical Patch Update - October 2014' Connection Issue Patch to all connecting ArcGIS clients.
  2. If necessary, connect as the SYS or SYSTEM user and grant the SDE user privileges to select from the v$parameter view.

    Code:
    SQL> GRANT SELECT ON v_$parameter TO SDE;

  3. Next, connect as the SDE user and execute the following procedure. The procedure inserts a new entry in the server_config table named open_cursors, with the value for the Oracle instances open_cursor parameter obtained from executing the dbms_utility.get_parameter_value subprogram.

    The procedure creates the open_cursors parameter in all ArcGIS geodatabases, the master instance and all project instances.

    Code:
    DECLARE
    rval INTEGER;
    ival INTEGER := 0;
    pname VARCHAR2 (256) := 'OPEN_CURSORS';
    sval VARCHAR2(256);
    gdb_name NVARCHAR2(256);
    cprop_val VARCHAR2(256) := NULL;
    c1 INTEGER;
    c1_status INTEGER;
    sqlstmt CLOB;

    CURSOR instances_curs IS
    SELECT instance_name
    FROM sde.instances;

    BEGIN
    rval := dbms_utility.get_parameter_value(pname, ival, sval);

    IF ival > 0 THEN
    OPEN instances_curs;
    FETCH instances_curs INTO gdb_name;

    WHILE instances_curs%FOUND
    LOOP
    sqlstmt :=
    'BEGIN '
    || gdb_name
    || '.svr_config_util.insert_server_config(:s1,:s2,:s3); '
    || 'EXCEPTION WHEN DUP_VAL_ON_INDEX THEN '
    || gdb_name
    || '.svr_config_util.update_server_config(:s1,:s2,:s3); '
    || 'END;';

    IF NOT dbms_sql.is_open (c1) THEN
    c1 := dbms_sql.open_cursor;
    END IF;

    dbms_sql.parse (c1, TO_CHAR (sqlstmt), dbms_sql.native);
    dbms_sql.bind_variable (c1, ':s1', pname);
    dbms_sql.bind_variable (c1, ':s2', cprop_val);
    dbms_sql.bind_variable (c1, ':s3', ival);

    c1_status := dbms_sql.execute (c1);
    dbms_sql.close_cursor (c1);

    FETCH instances_curs INTO gdb_name;
    END LOOP;

    CLOSE instances_curs;
    END IF;

    EXCEPTION
    WHEN OTHERS THEN
    RAISE;
    END;
    /

  4. After successfully executing the previous step, the SYS or SYSTEM user can revoke the select privilege on the v$parameter view from the SDE user.

    Code:
    SQL> REVOKE SELECT ON v_$parameter FROM SDE;

Related Information