注册

  HowTo:  Configure the geodatabase open_cursors server_config parameter for Oracle

【相关信息】
Article ID: 43377
Software:
ArcGIS Server (10.0 and prior) 9.3.1, 10
ArcGIS for Server 10.1, 10.2, 10.2.1, 10.2.2, 10.3
Platforms: N/A

【问题描述】
Because of the change in Oracle's Critical Patch Update - October 2014 release, ArcGIS is no longer able 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.
2015-12-04 添加评论
分享

没有找到相关结果

    已邀请:

    易智瑞技术支持

    赞同来自:

    【解决方案】
    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.
     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. 
    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.

    SQL> REVOKE SELECT ON v_$parameter FROM SDE;

    【其它相关参考】
    1. ArcGIS cannot connect to an Oracle database after installing Oracle Critical Patch Update for October 2014It has been reported that ArcGIS is no longer able to connect to Oracle after applying Oracle Critical Patch Update (CPU) for October 2014. ArcGIS connections hang, cause an error, or crash when attempting to establish a connection. This bug ap...


    【创建及修改时间】
    Created: 11/6/2014

    Last Modified: 11/10/2014
    【原文链接】
    http://support.esri.com/en/kno ... 43377
    回复时间：2015-12-05 10:19:08 0 0
    分享
    为什么被折叠? 0 个回复被折叠

    要回复问题请先登录或注册


    Copyright © 2025