ORA-00020: maximum number of processes cannot be stopped

Recently I just changed my job, but fortunately, I am still working on Database O & M. What is depressing is that the upgrade is shared. The background error is reported!

First let's talk about ora-00020 error! We all know the reason is that the number of processes has reached the upper limit.

Select count (*) from v $ process;

Show parameter processes

The two values are infinitely close or identical.

It is very easy for databases with low downtime requirements, that is, simply modify the value of the processes parameter. The specific process is as follows:

Alter system set processses = 2000 scope = spfile;

Shutdown immediate

Startup

Show parameter processes

Check whether the value has changed successfully. What should I do if I cannot stop the service?

 

I encountered such a tragic situation and was still depressed. But I did some work to ensure the normal operation of the system. It was my ancestor to work normally!

Query the current number of processes:

Select count (*) from v $ session;

View the number of inactive processes:

Select count (*) from v $ session where status = 'inactive ';

View which users have the most INACTIVE

Select username, count (*) from v $ session where status = 'inactive' group by username;

Fortunately, my system has a large number of INACTIVE connections, so we can delete them. In fact, I don't know if this will have a big impact on others. I told the leaders, but I decided to delete it.

Declare cursor mycur is
Select B. sid, B. serial # from v $ session B where B. username = 'username' and B. STATUS = 'inactive ';
Begin
For cur in mycur
Loop
Execute immediate ('alter system kill session ''' | cur. sid | ',' | cur. SERIAL # | '''');
End loop;

End;

Be careful! This can be done with the consent of the leader.

Oracle ORA-01555 snapshot old description

Troubleshooting for ORA-01078 and LRM-00109

ORA-01555 ultra-long Query Duration time

Notes on ORA-00471 Processing Methods

ORA-00314, redolog corruption, or missing Handling Methods

Solution to ORA-00257 archive logs being too large to store