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.

CONNECT_TIME

I had to limit a SQL*Plus session’s connection time today while I was setting up a new environment so I decided to document how I did it. The example below was run on an Oracle 11.2.0.2.7 database. First I connected as SYS and limited CONNECT_TIME to 1 minute in the DEFAULT profile: 

SQL> conn / as sysdba

Connected.

SQL> alter profile default

  2  limit connect_time 1

  3  /

 

Profile altered.

 

SQL>

 

Then I set RESOURCE_LIMIT to TRUE so that limits would be enforced:

 

SQL> alter system set resource_limit = true

  2  /

 

System altered.

 

SQL>

 

I created a user and gave it the DEFAULT profile. Then I connected as that user, looked at CONNECT_TIME in USER_RESOURCE_LIMITS then checked the time at 10 second intervals:

 

SQL> create user andrew

  2  identified by reid

  3  profile default

  4  /

 

User created.

 

SQL> grant create session to andrew

  2  /

 

Grant succeeded.

 

SQL> conn andrew/reid

Connected.

SQL> select limit from user_resource_limits

  2  where resource_name = 'CONNECT_TIME'

  3  /

 

LIMIT

----------------------------------------

1

 

SQL> select to_char(sysdate,'hh24:mi:ss')

  2  time_now from dual

  3  /

 

TIME_NOW

--------

17:31:40

 

SQL> host sleep 10

 

SQL> select to_char(sysdate,'hh24:mi:ss')

  2  time_now from dual

  3  /

 

TIME_NOW

--------

17:31:50

 

SQL> host sleep 10

 

SQL> select to_char(sysdate,'hh24:mi:ss')

  2  time_now from dual

  3  /

 

TIME_NOW

--------

17:32:00

 

SQL> host sleep 10

 

SQL> select to_char(sysdate,'hh24:mi:ss')

  2  time_now from dual

  3  /

 

TIME_NOW

--------

17:32:10

 

SQL> host sleep 10

 

SQL> select to_char(sysdate,'hh24:mi:ss')

  2  time_now from dual

  3  /

 

TIME_NOW

--------

17:32:20

 

SQL> host sleep 10

 

SQL> select to_char(sysdate,'hh24:mi:ss')

  2  time_now from dual

  3  /

 

TIME_NOW

--------

17:32:30

 

SQL> host sleep 10

 

Once the session had been connected for 1 minute, the next SQL statement failed and the session was terminated with an ORA-02399:

 

SQL> select to_char(sysdate,'hh24:mi:ss')

  2  time_now from dual

  3  /

select to_char(sysdate,'hh24:mi:ss')

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-02399: exceeded maximum connect time, you are

being logged off

ORA-02399: exceeded maximum connect time, you are

being logged off

 

SQL>

 

… and a subsequent attempt to run the SQL showed that the session was no longer connected:

 

SQL> select to_char(sysdate,'hh24:mi:ss')

  2  time_now from dual

  3  /

select to_char(sysdate,'hh24:mi:ss')

*

ERROR at line 1:

ORA-01012: not logged on

Process ID: 884

Session ID: 61 Serial number: 2851

 

SQL>

ORA-01012

This was tested on Oracle 9. If you are in the middle of a shutdown immediate in one session (in red): 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> 

... and you connect as SYS in another session (in blue), the session starts without an error message but, if you run any SQL, you get an ORA-01012: 

ANDREW > sqlplus '/ as sysdba'

 

SQL*Plus: Release 9.2.0.5.0 - Production on Wed Jun 6 14:35:47 2012

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

Connected.

SQL> select sysdate from dual;

select sysdate from dual

*

ERROR at line 1:

ORA-01012: not logged on

 

SQL>