SLTechnology News&Howtos › Database ›

ORA-00020: maximum number of processes (xxxx) exceeded error solution

Shulou Source: shulou.com Published: 2022-06-01 02:20:35 10月09日 Update

Today, java develops a large number of oracle data guides on the connection line, which will prompt you that you can't connect to the database. I can't connect locally with sqldeveloper.

Log in to the server, restart oracle, and still can't connect locally.

Log in to oracle in xshell

# su-oracle

# sqlplus / as sysdba connection Oracle

Prompt for a user name and password.

And report an error ORA-00020: maximumnumber of processes (300) exceeded

According to the error message, it is due to the maximum number of processes processes.

The normal method cannot log in, so we need to add the-prelim parameter when we connect.

# sqlplus-prelim/ as sysdba is finally logged into the SQL interface of Oracle

[refer to http://blog.sina.com.cn/s/blog_ad6555610101ar4t.html, http://blog.sina.com.cn/s/blog_ad6555610101ar46.html]

SQL > set linesize 500

SQL > show parameter processes

You can see that the default processes setting is 300. 0. It's too small. We'll have to change it later.

System production system, can not restart the database, fortunately, the system returned to normal after a while. It is mainly caused by a large number of database insertion and modification operations.

Solution:

When the system is idle, modify the system processes parameter to 1000 and restart the database. [the processes parameter is static. After modification, you need to start the database. ]

SQL > alter system set processes=1000 scope=spfile

SQL > SHUTDOWN IMMEDIATE

SQL > STARTUP

SQL > show parameter processes

You can see that the processes parameter has now been changed to 1000.

We'll do it again.

SQL > show parameter spfile; take a look at the path of the spfileora11g.ora file

Then exit the oracle console and go to the shell terminal interface.

# cp / data/oracle/product/11.2.0/db_1/dbs/spfileora11g.ora/tmp

# vim / tmp/spfileora11g.ora [this is a binary file, which will show some garbled code when opened, but we can see processes=1000, indicating that it has been written to the configuration file]