About ORA-01000: maximum open cursors exceeded problem analysis summary

Understanding about ora-01000: a point beyond the maximum number of opened cursors
ORA-01000: maximum open cursors exceeded
ERROR at line 1:
ORA-01000: maximum open cursors exceeded

Problem Analysis:
1. When the cursor is open, an error occurs and no close occurs.
2. It may be a problem with the table structure.
 
Solution:
Step 1:
View the current cursor count configuration of the database
Show parameter open_cursors;
 

Step 2:
View cursor usage:
Select o. sid, oSUSEr, machine, count (*) num_curs
From v $ open_cursor o, v $ session s
Where user_name = 'USER' and o. sid = s. sid
Group by o. sid, osuser, machine
Order by num_curs desc;
 
In user_name = 'user', user indicates the username of the database that occupies database resources.
 

Step 3:
View the SQL statement executed by the cursor:
Select o. sid q. SQL _text
From v $ open_cursor o, v $ SQL q
Where q. hash_value = o. hash_value and o. sid = 123;
 
Step 4:
Analyze whether the database access program is normal in resource release based on cursor usage. If the program releases resources normally, increase the number of cursors.
Alter system set open_cursors = 2000 scope = both;

The above is the processing method when the number of cursors is indeed insufficient.
However, sometimes open_cursor is increased. For example, I changed the cursor size to 300, or an error occurs, after 1000 began to appear the ORA-01001: invalid cursors. How to Increase
The number of open_cursors does not help. This is probably a problem with the table structure.
 

 

So how to verify it?
The case is from itpub, yangtingkun:
"Our system has a large amount of data, nearly 200 tables. Some tables need to insert about 1000000 pieces of data each day. The table is designed using ER/STDIO and then directly generates the table creation script. Because the table creation parameters in the physical_attributes_clause statement are not set, the default parameters are used. It seems that INITIAL 10 k next 10 k pctfree 20 PCTUSED 50, because the table's storage space is too small
It will fill up in a short period of time, so Oracle needs to continuously apply for space for more than 30 tables (with a large amount of data. So after a while, the ORA-01000: maximum open
Cursors exceeded error. I changed the cursor size to 300, or an error occurred, after which the ORA-01001: invalid cursors began to appear. How can we increase the number of open_cursors?
No. Using the select * from v $ open_cursors query, we found that hundreds of INSERT statement cursors were not released.
 
I started to suspect that it was a program call problem. I checked the program carefully and found no problem. However, after the TRUNCATE operation is performed on a table, all the inserted cursors of the table are released. Therefore, it is suspected that the table structure is used.
Problems. Reset the table creation parameter to set INITIAL and NEXT of the tables with the largest data volume to 50 MB. ."
 

FYI:
First, you can use the SQL _TEXT field in v $ open_cursor To Find Out What SQL statements are not released.
Due to improper table creation parameter settings, many INSERT statements cannot be released.
 

 

SELECT * from v $ OPEN_CURSOR WHERE SQL _TEXT LIKE 'insert %'
 
Find out which tables have more INSERT statements than released. Check whether the table creation parameters of the table are appropriate from ALL_TABLES.
 

Analysis of yangtingkun:
"Table parameters at the beginning
Pct free 10
Pct used 40
STORAGE (INITIAL 10 K
NEXT 10 K
MINEXTENTS 1
MAXEXTENTS 121
PCTINCREASE 0
)
9 K space is full in seconds. ORACLE will automatically apply for space when the next insert statement comes, and the cursor occupied by this statement cannot be released. The space applied for by ORACLE is immediately
Fill up, and then apply for a new space. If more than two or thirty tables repeatedly require ORACLE to perform the above operations within a few hours, it is estimated that a large number of cursors cannot be released. The above is my estimation ."

1 2 Next Page