ORA-01012: not logged on

ORA-01012: not logged on

ORA-01012: not logged on

This may occur generally due to heavy load or out of available connection in the database. If there is maximum number of sessions connected to the database, which is defined by ‘processes‘ parameter and database does not allow sysdba as well as other users to connect to the database. Sometimes it may also occur due to improper shutdown the database. In that case it shows connected but it does not allow to happening any query to the database instead it fails with ORA-01012: not logged on

Sometimes connecting with sysdba shows database in idle instance but whenever you issue startup it fails with ORA-01081: cannot start already-running ORACLE - shut it down first.

In the above case:

Either shutdown the Application as well as DB server (if Possible) or Kill unused oracle processes which holding the resource (Make sure do not kill BG processes. You can use toad for killing the process). Then login with sysdba to increase the value of parameter ‘processes’.

SQL> show parameter sessions

SQL> show parameter processes

SQL> show parameter transactions

SQL> SELECT value FROM V$PARAMETER

     WHERE NAME = 'processes';

SQL> Alter system set processes=300 scope=both sid='*';

SQL> Alter system set sessions=355 scope=both sid='*';

SQL> Alter system set transactions=410 scope=both sid='*';