Login to Oracle when max sessions exceeded

Quick tip - thanks to Russell Willcox for this one :)

See also :

 http://www.dba-oracle.com/t_connecting_hung_instance_using_prelim_option.htm

Login when max sessions exceeded (and you can’t log in using standard methods)

This is the backdoor entry method to access the database in a hung situation where no other methods are allowing you to create new sessions. 

This will not create any session in the database and connects to the SGA directly.

cmd> sqlplus –prelim “/ as sysdba”  (or… sqlplus –prelim sys/password as sysdba)

note: just try typing sqlplus –prelim / as sysdba (don’t cut and paste – it doesn’t work)

sql> shutdown abort

sql> startup restrict

sql> exit

cmd> sqlplus / as sysdba

sql> alter system set sessions=600 scope=spfile;

sql> alter system set processes=600 scope=spfile;

sql> alter system set parallel_max_servers=0 scope=both; (IF NECESSARY... i.e. parallel processes swamping database)

sql> alter database mount;

sql> alter database open;

sql> create pfile from spfile;

sql> alter system disable restricted session;