oracle11gsqlplus

ORA-0020: Maximum number of processes (150) exceeded

I tried login into sql plus. When i enter the username and password as system it displays the message maximum number of processes (150) exceeded. How i could resolve this error.

Solution

  • If you are a DBA or you have DBA rights you can do this as below:

    Steps to increase the number for processes/sessions at database level in Oracle is as follows

      1.  Log on to the database as system administrator

    • In SQL Plus enter: "connect / as sysdba"

      2. Set the number of processes to be desired value <num_processes>

    • In SQL Plus "alter system set processes={num_processes} scope=spfile"

      3. Unset the number of sessions to remove the default session limit

    • In SQLPlus "alter system reset sessions scope=spfile sid='*' "

      4. Shutdown the database

    • In SQL Plus "shutdown immediate"

      5. Startup the database

    • In SQL Plus "startup"

       6. Check the changes have taken effect.

    • In SQL Plus "show parameter sessions" and "show parameter processes"

    or run the query select value from v$parameter where name = 'processes'