ORA-00020: maximum number of processes (150) exceeded

Welcome back to HamidHelal blog.

Sorry for the long delay.

Today i encountered an error from client

ORA-12520: TNS:listener could not find available handler for requested type of server


when i tried to connect to database as admin, i got another error is


ORA-00020: maximum number of processes (150) exceeded


but there is a trick to connect

try,

C:\Users\Administrator>CD C:\app\Administrator\product\11.2.0\dbhome_1\BIN

C:\app\Administrator\product\11.2.0\dbhome_1\BIN>SQLPLUS /NOLOG

SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 20 00:38:14 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> CONN /AS SYSDBA
ERROR:
ORA-00020: maximum number of processes (150) exceeded




C:\app\Administrator\product\11.2.0\dbhome_1\BIN>sqlplus -prelim "/ as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 20 00:41:43 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> SHUTDOWN ABORT
ORACLE instance shut down.
SQL> EXIT
Disconnected from ORACLE



C:\app\Administrator\product\11.2.0\dbhome_1\BIN>SQLPLUS / AS SYSDBA

SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 20 00:43:31 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> STARTUP
ORACLE instance started.

Total System Global Area 1.0255E+10 bytes
Fixed Size                  2290120 bytes
Variable Size            1644170808 bytes
Database Buffers         8589934592 bytes
Redo Buffers               18817024 bytes
Database mounted.
Database opened.
SQL>

How to get connected user session and the application name, who are using ?

As salamualikum (islamic greetings), brothers and sisters.

 Huhh, Post again after a long time. I'm passing very busy time. Lot's of change around me.

Come to the topic. And it's about database session issue.

Here we will learn how to get all the open database session (open by different application) and also the session for a particular USER.

By this information you will kill the session and also terminate any session forcefully and do you necessary works with the database.

connect database with the SYS/SYSTEM user. And run the bellow code.

SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program,
       s.machine
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND';

it will show you all the open session for the database. If you need for a particular user, then run the bellow code.

SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program,
       s.machine
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND'
AND s.username='db_user';

If you want to get just in a easy SQL, then run the bellow code,

select sid
           ,serial# 
from v$session 
where username = 'db_user';

Now, you get all the required information to terminate the specific session. How will you do this ?

You need to follow the following syntax.

alter system kill session '<sid>,<serial#>';

alter system kill session '39,1232';

You are done...!!!

Do you need to close all the session for a specific user ?

Then you will need a LOOP to close one by one. Follow the bellow example.

begin    
    for x in ( 
            select Sid, Serial#, machine, program 
            from v$session 
            where 
                username = 'db_user' 
        ) loop 
            execute immediate 'Alter System Kill Session '''|| x.Sid 
                     || ',' || x.Serial# || ''' IMMEDIATE';
            dbms_output.put_line('Connected Session Found from '||x.machine||', Via '||x.program);                   
        end loop; 
    dbms_output.put_line('Ops! No Connected Session Found.');
end;


End for the day....

Praise to Almighty Allah(swt).  :)

Fi amanillah

How to Get Number of Days In a Year ?

If you cannot calculate how many days in a year, here is an easy example for you. Write down the bellow code directly in sql*plus or SQL Developer or any others tools and get the result. :)

SELECT ADD_MONTHS(TRUNC(SYSDATE,'RRRR'),12)-TRUNC(SYSDATE,'RRRR')
FROM DUAL;

Hope it helps...

If any query, just leave your comments...