Using Oracle profile to control session idle time

The customer wants to control the session idle time, here is an example.
Microsoft Windows [version 6.1.7601]
Copyright (c) Microsoft Corporation. All rights reserved.

C:\users\liubinglin>sqlplus Sys/oracle[email Protected]:1521/hello as Sysdba

Sql*plus:release 11.2.0.3.0 Production on Tuesday April 14 08:42:55 2015

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

Connect to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-production
With the partitioning, OLAP, Data Mining and Real application testing options

Sql> Create profile Test_profile limit Idle_time 1; Indicates that the allowed idle time is 1 minutes.

Configuration file has been created

Sql> Set Linesize 200
Sql> SELECT * from Dba_profiles where profile= ' test_profile ';

Profile Resource_name RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
Test_profile Composite_limit KERNEL DEFAULT
Test_profile Sessions_per_user KERNEL DEFAULT
Test_profile cpu_per_session KERNEL DEFAULT
Test_profile Cpu_per_call KERNEL DEFAULT
Test_profile logical_reads_per_session KERNEL DEFAULT
Test_profile Logical_reads_per_call KERNEL DEFAULT
Test_profile idle_time KERNEL 1
Test_profile connect_time KERNEL DEFAULT
Test_profile PRIVATE_SGA KERNEL DEFAULT
Test_profile failed_login_attempts PASSWORD DEFAULT
Test_profile password_life_time PASSWORD DEFAULT

Profile Resource_name RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
Test_profile password_reuse_time PASSWORD DEFAULT
Test_profile Password_reuse_max PASSWORD DEFAULT
Test_profile password_verify_function PASSWORD DEFAULT
Test_profile password_lock_time PASSWORD DEFAULT
Test_profile password_grace_time PASSWORD DEFAULT

16 rows have been selected.

Sql> Show Parameter Resource

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Resource_limit Boolean FALSE
Resource_manager_cpu_allocation Integer 4
Resource_manager_plan string

Sql> alter system set RESOURCE_LIMIT =true; Control of items of type kernel in profile you need to set this parameter to an item of type True,password is not limited by this parameter.

The system has changed.

Sql> create user test111 identified by test111 default tablespace users temporary tablespace temp profile test_profile;

The user has created.

Sql> Grant Connect,resource to test111;

Authorization is successful.

Another window opens a session:
C:\users\liubinglin>sqlplus Test111/[email Protected]:1521/hello

Sql*plus:release 11.2.0.3.0 Production on Tuesday April 14 08:55:49 2015

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


Connect to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-production
With the partitioning, OLAP, Data Mining and Real application testing options

Sql> CREATE TABLE Test (ID number);
CREATE TABLE Test (ID number)
*
An error occurred on line 1th:
ORA-02396: Maximum idle time exceeded, please reconnect

After a minute of idle operation, you will receive the above error.

But the customer said that the above method is only applicable to Sqlplus, the PL/SQL tool is invalid, the following discusses why the PL/SQL is invalid.

View database session information after using test111 to log on PL/sql:

After successful landing in the database to see the creation of two sessions, you can see the session login time is 11:17:09 and 11:17:28 two time points. Since no SQL is executed, the session state after successful login is inactive.

Idle_time is set to 1 minutes, and 1 minutes later the state of the two sessions becomes sniped, indicating that the session has expired.

PL/SQL does not error and executes successfully at any time when execution is performed in PL +.

But from the background, the landing time has become 11:20:47 and 11:20:51, the state has become inactive.

Indicates that the SQL statement is automatically re-logged when the PL + + is executed.

Here is the case for Sqlplus:

11:37:26 after successful landing, a session was created for Sqlplus,

The session becomes sniped after 1 minutes of inactivity.

Once again to the session operation, you receive the following error:

From the previous picture can be seen from the background to see Sqlplus's session has been killed.

Thus, it can be judged that the profile Idle_time is valid for Sqlplus, which is very much related to PL/SQL invalidation with the client.

Through this experiment can also be found that after the session expires, the State of the session will become sniped, the session will not be killed immediately until the next time the client of the session execution of SQL is killed, indicating that the session corresponding to the server process has been present, if such a lot of sessions, and the presence of sniped persists for a long time, the database may exceed the limit of processes initialization parameters.

Also explain here the meaning of the configuration sqlnet.expire_time parameter in the Sqlnet.ora configuration file:

Sqlnet. Expire_time=1 said that every 1 minutes to the client issued a test connection to the package, the client will receive a response, if the connection is normal, the connection will not be killed.

This parameter is used to resolve client-side shutdown, network failure, and then specify the time to kill the server process.

Oracle recommended profile is used with sqlnet.expire_time, but because of the features of the PL/SQL tool itself, it becomes sniped (the profile idle_time timeout) after the session's status. The first time the operation is automatically reconnected, so neither of these methods can control it.

Oracle proposes a way to deploy scripts that periodically kill sniped state sessions on the Oracle server side. But to deal with a small number of PL/SQL clients, it's a bit of a hassle.

--end--

Ext.: http://www.linuxidc.com/Linux/2015-05/117055.htm

Using Oracle profile to control session idle time