Friday, May 23, 2014

sqlplus -prelim

If all the available processes in a database are used up, you will get an ORA-00020 when you try to login. The best way round this is to get some people to log out. However, this may not be possible e.g. if people have logged in from some application then killed it, leaving their sessions alive in the database but with no way to get back to them and end them tidily.
 
Oracle 11: sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 13 15:56:40 2013
 
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
ERROR:
ORA-00020: maximum number of processes (100) exceeded
 
Enter user-name: ^C
Oracle 11:
 
An alternative is to login with the –prelim option. This does not allow you to run any SQL but it does let you close the database:
 
Oracle 11: sqlplus -prelim / as sysdba
 
SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 13 15:56:59 2013
 
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
SQL> select sysdate from dual;
select sysdate from dual
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0
 
SQL> shutdown abort
ORACLE instance shut down.
SQL>

Before opening the database again, you might consider increasing the processes parameter to reduce the chance of this happening again.



No comments: