Error ORA-01000: maximum open cursors exceeded

0 Likes

This article describes a potential solution for the error ORA-01000.

Problem:

A COBOL program that performs numerous file operations (OPEN, READ, WRITE, DELETE, REWRITE, CLOSE, and so on) has the potential to create a large number of cursors if no COBOL COMMIT exists in the program code. If left unmanaged, it is possible to run out of cursors and receive an Oracle error "ORA-01000: maximum open cursors exceeded" as shown in the run-time trace below.

23:35:50.384 0 MF.RTS 51 1 : "ERROR CODE = [1000]"
23:35:50.384 0 MF.RTS 51 1 : "ORA-01000: maximum open cursors exceeded"

Resolution:

There are two ACUFH configuration variables that may be helpful in managing cursors counts.

Note: Both of these variables may require some experimentation and discussion with your Database Administrator to determine what works best for your application and system.

The first variable is A_ORA_MAX_FILE_CURSORS, which by default is set to “0”. If this variable is not specified or set to the default value, the run-time system will use all the cursors that it can. When this variable is set to a value higher than the minimum requirement of “2”, if all of the allotted cursors for a file have been used, the least recently used cursor for that file is freed. It is advisable to start with a fairly low value, perhaps between 5 and 7. Be sure to set this variable in the ACUFH configuration file, because it is read only at startup time.

The second variable is COMMIT_COUNT, which indicates the conditions under which you want to issue an automatic COMMIT-WORK operation. Each time a COMMIT-WORK is issued, cursors are released. Thus, the greater the value of COMMIT_COUNT, the larger the number of cursors used and held until the next COMMIT-WORK is issued. Please see the excerpt from the Micro Focus Database Connectors documentation below.

Micro Focus Database Connectors > Reference > Configuration Variables > COMMIT_COUNT configuration variable

The value of COMMIT_COUNT indicates the conditions under which you want to issue an automatic COMMIT-WORK operation.

COMMIT_COUNT <value>

Valid values are:

"0"         A commit is issued when no locks are held, either because all files that had locked records have been closed, or because a COBOL COMMIT verb has been issued. This is the default value.
"n" A commit is issued after n operations. WRITE, REWRITE, and DELETE count towards n; READ, START, and READ NEXT do not.
"-1" No commit is issued by the Connector. When COMMIT_COUNT is set to "-1", there are two alternate ways to perform a commit or rollback: One way is to call your Oracle query tool with COMMIT-WORK or ROLLBACK WORK The second way is to use the COBOL verbs COMMIT and ROLLBACK, available in Server Express COMMIT_COUNT is set to "-1" internally when you use the transaction management facilities available in the Compiler. A COMMIT-WORK, however, is issued on exit from the run-time system (for example, on execution of a STOP RUN)

Incident Number: 2288050

Old KB# 14513
Comment List
Anonymous
Related Discussions
Recommended