Update open_cursors setting in geodatabases in Oracle

By default, the geodatabase assumes an Oracle open_cursors setting of 300. The geodatabase does not have access to the open_cursors value set for the Oracle database; therefore, if you set your Oracle database open_cursors parameter to a value other than 300, execute the sde.gdb_util.update_open_cursors stored procedure to synchronize the geodatabase's open_cursors setting with that of the Oracle database in which it resides.

Tip:

The sde.gdb_util.update_open_cursors stored procedure updates the open_cursors parameter stored in the master sde geodatabase repository and any user-schema geodatabases that are present in the Oracle database. Therefore, you do not need to run the procedure separately for user-schema geodatabases.

If you do not synchronize the open_cursors setting between the Oracle database and the geodatabase, activities that require multiple connections to the geodatabase (such as editing and querying geometric networks) could fail.

Beginning with 10.5.0.1.4 version geodatabases in Oracle, the sde.gdb_util.update_open_cursors stored procedure will be present in the geodatabase, and you can proceed with the following steps to synchronize the open_cursors setting:

1. Connect to the geodatabase using sys as sysdba from an SQL client.
2. If your geodatabase is in an Oracle 12c database, you must grant privileges to the sde user to run the sde.gdb_util.update_open_cursors stored procedure.

   GRANT INHERIT PRIVILEGES ON USER SYS TO SDE;
   

3. Execute the sde.gdb_util.update_open_cursors stored procedure.

   EXECUTE sde.gdb_util.update_open_cursors;
   

4. For geodatabases in Oracle 12c, you can revoke the inherit privilege you granted to the sde user in step 2.

   REVOKE INHERIT PRIVILEGES ON USER SYS TO SDE;
   

The database and geodatabase open_cursors settings are now synchronized.

If you update the Oracle open_cursors setting in the future, run this stored procedure again.