Wednesday, February 20, 2013

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
 

No comments: