How to fix 'ORA-01000 maximum open cursors exceeded' issue in Oracle database in RS

Cursors in Oracle

A cursor is a temporary work area created in the system memory when a SQL statement is executed. It can hold more than one row but can process only one row at a time. A set of rows the cursor holds is called an active set. The cursor might be used for retrieving data on a row-by-row basis like a looping statement.

ORA-01000 maximum open cursors exceeded

By default, Oracle database allows 300 open_cursors per connection session. Syncfusion Report Server tries to execute 300 SQL statements per connection by considering the default open_cursors limit. An issue occurs when the open_cursors limit is less than 300 in the Oracle server.

Solution to overcome the issue

Increase the open_cursors count limit to solve the problem. Oracle provides the following two different types of parameter files to solve this problem: PFILE and SPFILE.

Parameter File (PFILE)

A PFILE is a text file for setting the database initialization parameters. This PFILE is read at the instance start-up time to get specific instance characteristics. It can be edited in an editor such as vi on UNIX or Notepad on Windows. Any changes made in the PFILE will take effect only when the database is restarted.

You can increase the open_cursors count limit by editing the PFILE (init.ora) and restarting the database.

Since, the SPFILE has many advantages over PFILE, it is recommended to use SPFILE. Also, a solution for this issue is provided using the SPFILE below.

Server Parameter File (SPFILE)

A SPFILE is a binary file that contains the same information as PFILE. SPFILE permits dynamic changes without restarting that instance. By default, if you do not specify the PFILE in your STARTUP command, Oracle will use SPFILE. It cannot be directly edited since it has a header and footer that contain binary values. Oracle allows you to manage the SPFILE via the ALTER SYSTEM command.

When you execute the ALTER SYSTEM command, the parameter change is validated immediately, which helps you to avoid errors associated with entering an incorrect parameter name or invalid value. An alternate system allows you to specify whether you need to update the parameter immediately, update just the SPFILE, or both. All these features help in avoiding human error associated with the manual updating of a PFILE.

To increase the open_cursors count limit in the SPFILE, use the following command.

SQL

alter system set open_cursors = 1000 scope=both;

Furthermore, to resolve ORA-01000 issue, try to close whatever cursors are no longer in use.