Howard Bash
asked on
ORA-01000: maximum open cursors exceeded
I have an application that iterates through a flat file and parses out some fields and inserts those fields into a table. I have previously run this in a test environment and inserted over 8K rows without a problem.
The Oracle running on the server was updated and now I can only insert 444 rows. I make it a point to close connections and command objects but still have this problem.
Please advise.
The Oracle running on the server was updated and now I can only insert 444 rows. I make it a point to close connections and command objects but still have this problem.
Please advise.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I close and dispose of all connection and command objects per row (added to see if it corrects this). Also, in my googling I see that Oracle "apparently" has some issues with freeing up connections even after the underlying code requests this. Many matches for this error with most responses alluding to a problem with Oracle in this regard.
Regarding modifying the server connections: it's a production server and I cannot do that.
In sniffing around more for answers what I found that seems to work (I will do more testing) is to every say 200 inserts, I call garbage collection for the vb.net application and that somehow does the trick. Not clear what is happening to make Oracle release these connections, but have successfully inserted 9K rows with this addition of calling garbage collection.
Regarding modifying the server connections: it's a production server and I cannot do that.
In sniffing around more for answers what I found that seems to work (I will do more testing) is to every say 200 inserts, I call garbage collection for the vb.net application and that somehow does the trick. Not clear what is happening to make Oracle release these connections, but have successfully inserted 9K rows with this addition of calling garbage collection.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
All good suggestions. However te GC.Collect() call worked.
Open in new window
To raise the parameter:
Open in new window
I agree with the previous comment that SQL*Loader or (preferrably) external tables are the way to go for what you are trying to accomplish.