Use Oracle PROFILE to control session idle time

Use Oracle PROFILE to control session idle time

The following is an example of how the customer wants to control the idle time of a session.
Microsoft Windows [version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.
 
C: \ Users \ LIUBINGLIN> sqlplus sys/Oracle123 @ localhost: 1521/hello as sysdba
 
SQL * Plus: Release 11.2.0.3.0 Production on Tuesday April 14 08:42:55 2015
 
Copyright (c) 1982,201 1, Oracle. All rights reserved.
 
Connect:
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 idle time is 1 minute.
 
The 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; to control KERNEL projects in PROFILE, you must set this parameter to TRUE. PASSWORD-type projects are not limited by this parameter.
 
The system has been changed.
 
SQL> create user test111 identified by test111 default tablespace users temporary tablespace temp profile test_profile;
 
The user has been created.
 
SQL> grant connect, resource to test111;
 
Authorization successful.
 
Open a session in another window:
C: \ Users \ LIUBINGLIN> sqlplus test111/test111 @ localhost: 1521/hello
 
SQL * Plus: Release 11.2.0.3.0 Production on Tuesday April 14 08:55:49 2015
 
Copyright (c) 1982,201 1, Oracle. All rights reserved.
 
 
Connect:
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)
*
Row 3 has an error:
ORA-02396: Maximum idle time exceeded. Try again
 
If you do not perform this operation after one minute, you will receive the above error.

However, the above method only applies to SQLPLUS and is invalid for PL/SQL tools. The following describes why PL/SQL is invalid.

Use test111 to log on to PL/SQL and view the database session information:

After successful login, two sessions are created in the database. The login time of the session is 11:17:09 and 11:17:28. Since no SQL statement is executed, the session status after successful login is INACTIVE.

IDLE_TIME is set to 1 minute. After 1 minute, the status of the two sessions changes to SNIPED, indicating that the session has expired.

When any SQL statement is executed in PL/SQL, no error is reported in PL/SQL and execution is successful.

However, from the background, the logon time changes to 11:20:47 and 11:20:51, And the status changes to INACTIVE.

It indicates that the system automatically re-logs in when PL/SQL executes the SQL statement.

The following is the case of SQLPLUS:

After successful login at 11:37:26, a SESSION is created for SQLPLUS,

The session Changes to the SNIPED state after 1 minute of no operation.

When the session operation is performed again, the following error is returned:

From the previous figure, we can see that the sqlplus session has been killed in the background.

It can be determined that the PROFILE IDLE_TIME is effective for SQLPLUS, And the invalid PL/SQL is closely related to the client.

Through this experiment, we can also find that after a session expires, the session status changes to SNIPED, and the session will not be killed immediately until the client corresponding to the session is killed when the next SQL statement is executed, this indicates that the server process corresponding to the session has been present for a long time. If there are many such sessions and the status of SNIPED persists for a long time, the database may exceed the PROCESSES initialization parameter limit.

In addition, here we will explain the meaning of configuring the sqlnet. EXPIRE_TIME parameter in the SQLNET. ora configuration file:

SQLNET. EXPIRE_TIME = 1 indicates that a test connection packet is sent to the client every one minute. After receiving the packet, the client returns a response. If the connection is normal, the connection will not be killed.

This parameter is used to solve the problem where the client is shut down for no reason, the network fails, and the server process is killed within the specified time.

PROFILE and SQLNET. EXPIRE_TIME is used together. However, due to the characteristics of the PL/SQL tool, it will automatically reconnect when the SESSION status changes to SNIPED (PROFILE IDLE_TIME times out, so neither of these methods can control it.

Oracle proposes a method to deploy a script to regularly kill a SNIPED Session on the Oracle server. However, in order to process a small number of PL/SQL clients, it is a little expensive.

-- End --