Connect after automatic disconnect in Oracle 8x

In real-world database applications, we often encounter the problem that a user who connects to an Oracle database does not have a subsequent operation after an operation, but is disconnected from the database for a long time. For a small application system, the number of connections is limited, which seems to have no serious consequences, but if for a large database application. such as taxation, industry and commerce, if the number of connections to the database, for the database server, more than one connection will consume a resource, if a large number of users connect into the database system but do not carry out any operation, this invisible in vain caused the waste of server system resources, but also caused the increase in server load, For those who do work, it is not possible to maximize the use of server resources, in severe cases can cause a sharp decline in system performance.

What should we do to address this problem? For the current popular three-tier architecture (Browser/application/server) Development, this problem can be avoided by setting up the application server-side Connection Sharing pool (shared pool). But for the traditional two-tier structure (client/server) application, we must intervene to avoid such a waste of resources. Specific can be a background task to monitor all the processes in the system, for those idle more than a certain period of time to take certain special treatment measures, such as in the client prompt user connection time is too long, if no subsequent operating system will automatically kill the connection or directly to kill the idle connection. The following is a detailed discussion of how to automate user process monitoring in Oracle 8x environments and how to handle the connection to more than a certain amount of idle time.

One, identify the system over a certain amount of idle time connection

To enable background tasks to automatically handle more than a certain amount of idle connections, the first step is to identify those connections from all connections to the database server that need to be processed, that is, to obtain the logon time for each user connected to the server and the amount of free time after the last operation. In Oracle systems, there is a dynamic performance view v$session, which holds various dynamic information for the system's current connections. Of these, there are two fields Logon_time and Last_call_et can get the above two answers.

L Logon_time is a date-type field for user login time;

L Last_call_et is a number field, which means the time, in seconds, after the last statement is executed by the user. Each time the user executes a new statement, the field is reset to 0 and the count restarts. We can use this field to get a user's free time after the last operation of the database.

The following SQL query statement can get some basic information about all users connected to the current database, such as user name, status, name of the connection machine, name of the user in the operating system, process number of UNIX system, disconnected statement at UNIX operating system level, disconnected statement of Oracle database system, Landing time and the last operation to the current free time and so on.

SELECT s.username 用户名称, s.status 状态,s.machine 机器名称,
　　　　 osuser 操作系统用户名称,spid UNIX进程号,
'kill -9 '||spid UNIX级断开连接,
'alter system kill session ' ||''''||s.sid||','||s.serial# || ''';' Oracle级断开连接,
TO_CHAR (logon_time, 'dd/mm/yyyy hh24:mi:ss') 登陆时间,
last_call_et 空闲时间秒,
TO_CHAR (TRUNC (last_call_et / 3600, 0))||' '||' HRS '||
TO_CHAR (
TRUNC ((last_call_et - TRUNC (last_call_et / 3600, 0) * 3600) / 60, 0)
) ||' MINS' 空闲时间小时分钟,
module 模块
FROM v$session s, v$process p
WHERE TYPE = 'USER'
AND p.addr = s.paddr
AND status != 'KILLED'
-- AND SUBSTR (machine, 1, 19) NOT IN ('机器名')
AND last_call_et > 60 * 60 * 1 -- 空闲时间超过1小时的连接
ORDER BY last_call_et desc;

In the above query, we can shield some machines from the substr (machine, 1,) not in (' Machine name '), which may take a long time to run SQL statements or other special cases of machines. The reason for shielding these machines is that they are not processed in the background automatic recognition and processing tasks in the back.