Bug: Executing a compress when running on ArcSDE for Oracle results in a Network I/O error
Executing sdeversion -o compress or the compress command from ArcCatalog results in a Network I/O error when running on ArcSDE for Oracle.
The compress operation can encounter a Network I/O error when the process is unable to open an Oracle cursor for executing SQL SELECT or DML (INSERT, UPDATE, DELETE) statements.
ArcSDE for Oracle is highly optimized for performance and scalability by maintaining a set of open cursors for performing frequently executed SELECT and DML statements. If Oracle cursors are not preserved and closed after every execution, this prevents the Oracle instance from scaling to support a large number of concurrent users.
If the compress operation encounters a Network I/O error, the first step is to ensure that the value for the Oracle init.ora parameter open_cursors is set to a relatively large value. The value one needs to set for the parameter is dependent upon the number of registered tables in the ArcSDE instance.
Oracle's default value of 300 is typically too low. Setting the open_cursors parameter to a value of 2000 or 3000 should be sufficient. There is no harm setting the value too large. A larger value does not consume additional resources. System resources are not consumed until a cursor is explicitly opened.
To check the value for the open_cursors parameter, one can connect in SQL*Plus as the SYS or SYSTEM user and execute the following command:
SQL> SHOW PARAMETERS open_cursors
NAME TYPE VALUE
------------------------------------ ----------- -----
open_cursors integer 300
To alter the parameter to increase the value, use the ALTER SYSTEM command:
SQL> ALTER SYSTEM SET open_cursors = 2000;
If the Oracle instance is using a spfile.ora for managing instance parameters, make sure to include the 'scope = both' when executing the ALTER SYSTEM command.
SQL> ALTER SYSTEM SET open_cursors = 2000 scope = both;
Once the parameter has been set, execute the compress command. If the compress operation continues to encounter a Network I/O error, contact ESRI Support Services.
- What is an appropriate value for the Oracle init.ora parameter 'open_cursors'?
- err-1000: maximum open cursors exceeded when performing an ArcGIS operation