Database reach max process or sessions, son no new login is available, first your sysadmin cant access too for solve the problem seting new process value, is a production enviroment so we dont want to kill the instance as a first step to solve the problem .
In linux will be easy make room for enter killing one session but in windows
OS we have a little problem. In windows we cant see session in threads, all are in one process oracle.exe, so no solution? only restarting instance? no.
One easy way to solve the issue, in windows in services.msc you can stop
OracleDBConsole[SID] give you the chance of conect without reboot instance service.
Imagine on windows we cant enter on databsase and service not work for restart instance, we cant kill SMON as in linux,PMON realizes that SMON is missing and aborts the database.
Beyond oracle 10g oracle introduced a preliminary connection, to bypass the creation of a new session in cases where the database is hung and not accepting traditional sqlplus connections, this type of conection database allow sysadmin to shutdown abort the database, but take care and first dump some traces
sqlplus -prelim sys/oracle as sysdba
@> set _prelim on
@> conn sys/oracle as sysdba
We can’t query regular tables nor even V$ views, because we aren’t really logged on! Because of the prelim option we do not have all the structures for query execution set up.
How to dump traces:
sqlplus system/mypass -prelim /nolog
oradebug hanganalyze 12
oradebug dump systemstate 10
oradebug tracefile_name C:\oracle\admin\orcl\udump\orcl_ora_5598.trc
We can now check this dump to take note of details of the problem:
os id: 789
process id: 27, oracle@orcl (TNS V1-V3)
session id: 77
session serial #: 56789
killing that session, will solve the problem.
linux: kill -9