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. If your geodatabase is in an Oracle 12c or newer release 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. 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.