Oracle's ORA-00604 Error case learning

ORA-00604 error occurred at recursive SQL level string

Cause:an error occurred while processing a recursive SQL statement (a statement applying to internal dictionary tables).

Action:if the situation described in the next error on the stack can is corrected, do; Otherwise contact Oracle Customer Support.

ORA-00604: An error occurred while recursion to a SQL layer

Cause: An error occurred while running a recursive SQL statement that would apply to an internal table or data dictionary operation.

Scenario: If the stack of errors described above can be repaired, repair and continue, otherwise, please contact Oracle Customer service. That, of course, is the Oracle's official solution. I once remembered a master who summed up the solution to the ora-00604/ora-04031 problem:

Modify Init.ora

Add to

_db_handles_cached = 0

and restart the database.

Analysis: ORA-00604 This information indicates that an error occurred while the database was executing an internal SQL statement. For example, to insert a row of data into a table, there is no space to expand. Oracle then goes to search for where the next expansion space can be built, how large it is, but not successful. In general, when a ORA-00604 error occurs, it is accompanied by other errors, such as: ORA-1547.

First, you should check the warning file AlertSID.log to find information about the ORA-600 class.

The most common reason for this error is that the parameter Open_cursors value in the database file Initsid.ora is too small. You can modify the Initsid.ora file, and the Open_cursors value is typically 255. After the modification, the Oracle is down and then restarted.

You can also set up and start event tracking for the database. Add a line to the Initsid.ora:

event = "00604 trace name errorstack"

Downtime and restart Oracle to make this event-tracking parameter work. This way, when a ORA-604 error occurs, the information is saved in the trace file.

Other causes of ORA-604 errors may be:

-in Initsid.ora, the parameter dc_free_extents or row_cache_enqueues is too low. Depending on the operating system and the database, the values of these two parameters can be appropriately increased, and Oracle will be down and restarted.

-run out of space (accompanying ORA-1547 error). At this point, to add a new file to the tablespace, increase the size of the table space.

-Reached Max_extents (accompanied by ORA-1556 error). If so, modify the table to allow for more extensions. Please find the maximum value of max_extents from the technical manual. If the maximum value has been reached, the table must be unloaded (export) and then imported into the database using the Compress extents option.

Case ONE: Oracle executes recursive query with error

Problem Description: I often encounter ORA-00604 and ORA-01000 (the maximum number of open cursors) error. However, when I examine the code, all the result sets and statement objects are closed in the final block (I'm using JDBC). The query I executed is an Oracle recursive query (starting with this and connecting through this). Can you tell me where the problem is, and in what circumstances will this error occur?

Solution: The Open_cursors parameter values in the Init.ora file may be set too low. The default value for this parameter is very low (50). It should be set to 200 or higher. Even if you close the result set, you are not shutting down the SQL statement in Java code, which can cause this problem.

If set to Yes, make sure that your active connection pool is enabled (for performance reasons), otherwise set to No.

Ask your database administrator to monitor the database and see entries using the V$open_cursors and V$sysstat data dictionary views.