ORA-01000:maximum open cursors exceeded

ORA-01000:maximum open cursors exceeded

Cause:A host language program attempted to open too many cursors. The initialization parameter OPEN_CURSORS determines the maximum number of cursors per user.

Action:Modify the program to use fewer cursors. If this error occurs often, shut down Oracle, increase the value of OPEN_CURSORS, and then restart Oracle.

ORA-01000: "maximum open cursors exceeded":

This error occurs when the number of cursors currently open by a single database connection have exceeded the value of the "open_cursors" paramerer in the "init.ora" file.

You can find the current maxiumum value allowed using "show parameter open_cursors" from SQL*Plus or Server Manager.

This is a per user limit, and is the number of cursors which have been opened but not closed since the database connection was made. A large limit is typically required when performing a large number of queries since the last commit, such as validating a catalogue. The cause is usually "recursive SQL", e.g. having to extend a table or firing of triggers.

There may be a bug somewhere in the application so that a series of cursors are being opened and not closed.

As a workaround, the user could log out and log back in again, going straight to the function which caused the error. If this resolves the problem, it is likely that cursors were remaining open from a previous function.

ORA-00997: illegal use of LONG datatype

ORA-00997: illegal use of LONG datatype

Cause: A value of datatype LONG was used in a function or in a DISTINCT, WHERE, CONNECT BY, GROUP BY, or ORDER BY clause. A LONG value can only be used in a SELECT clause.

Action: Remove the LONG value from the function or clause

ORA-00997: "illegal use of LONG datatype":

When trying to reorganize a database table that includes a LONG column, there is likely to be an Oracle error message such as:

ORA-00997: illegal use of LONG datatype

This is because it is not possible to use a LONG column in any SQL statement. It can only be used where the application is retrieving part of the column - by specifying the start byte and the number of bytes.

The following will cause the above error:

ALTER TABLE tablename MOVE TABLESPACE tablespacename;

or

CREATE TABLE newtablename AS SELECT * FROM oldtablename;

ORA-00055: maximum number of DML locks exceeded

ORA-00055: maximum number of DML locks exceeded

Cause: Ran out of DML lock state objects.

Action: Increase the value of the DML_LOCKS initialization parameter and warm start.

ORA-00055 maximum number of DML locks exceeded:

This tends to be an intermittent problem, and occurs at moments of peak usage in an Oracle database.

It can be resolved by changing a parameter in the "initSID.ora" file:

dml_locks = 200

Basically, if the DML Locks limit has been set at 200, then

. 200 people could each be updating one table at a time
· or 20 people could all be updating 10 tables at a time,
· or 1 user could be doing an account number rename and using 100 tables while 10 other people could be updating 10 tables.

These updates do not need to be the same table.

The number of locks required increased from Oracle8 onwards since partitioned tables would use a lock for each partition.