Max number of sessions exceeded

[mooks]

I have encountered the following errors and I could not even login to the database, I have to shutdown the database instead, anyway of solving this problem?

SMON: following errors trapped and ignored:
ORA-00604: error occurred at recursive SQL level 1
ORA-00018: maximum number of sessions exceeded

Could this have happened if my utl_file connections were not closed properly?

Thanks!

[bk_raavi]

Increase the No Of Session Parameter.

[jmodic]

Originally posted by mooks
Could this have happened if my utl_file connections were not closed properly?

No, defenitely not.

[mooks]

What can I do to prevent it from happening beside increasing the number of sessions? Or any alert or warnings?

[mooks]

Any ways to track what could have caused it to happen? The recursive sql? There are no trace of the SQL in the alert log.

[jmodic]

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
or
- 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
or
- 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?

[mooks]

Hi

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?