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 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 cursors could fail.

The sde.gdb_util.update_open_cursors stored procedure is present in the geodatabase. Follow these steps to synchronize the open_cursors setting using the stored procedure:

1. Connect to the geodatabase using sys as sysdba from an SQL client.
2. 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. You can revoke the inherit privilege you granted to the sde user in step 2.

   REVOKE INHERIT PRIVILEGES ON USER SYS FROM SDE;

The database and geodatabase open_cursors settings are now synchronized.

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