Recursive (or any kind of) SQL statements have nothing to do with maximum number of sessions exceded! Oracle is not complaining about maximum naumber of cursors opened, it is complaining because there is too many sessions on your system!
The reason could be that either
- you have physical limit of max sessions in your init.ora too low
- something (firewall or something like that) is terminating the connections to the database in a "ungracefull"v maner and PMON is not able to clean those terminated sessions from the RDBMS
- something else ....????
You should check regulary after your database has been up for some time if the number of database sessions is constantly increasing. If yes, you should check if there is many sessions with status KILLED that will not go away even after quite a long time since their last activity.
Do you have MTS configuration?
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
There are no MTS configuration set. The sessions in the database is rather constant. It happened suddenly and after I shutdown the database, there are some processes which I cannot even kill (using kill -9), I have to reboot the whole server instead! Why is this so? Any ways to detect which process is causing it? Any logs?