Connect when Oracle 8x is automatically disconnected

The ORACLE tutorial you are looking at is: connect after Oracle 8x disconnects automatically.

In actual database applications, we often encounter a problem where a user connected to the Oracle database has no subsequent operations after one operation, but has not been disconnected from the database for a long time. For a small application, the number of connections itself is limited, which doesn't seem to have serious consequences, but for a large database application. Such as taxation, industry and commerce, etc., if the database connection number of the many, for the database server, 1 more than one connection will consume more resources, if a large number of users connected into the database system but I don't make any operation, this invisible in vain for server system resources waste, at the same time causes the increase of the load on the server, for users who do at work, will not be able to maximize the use of server resources, severe cases can cause the sharp decline in performance of the system.

How to deal with this kind of problem? This problem can be avoided by setting up the application server side connection sharing pool (shared pool) for the current popular three-tier structure (Browser/Application/Server) development. However, for the traditional two-tier structure (Client/Server), we must intervene to avoid such a waste of resources. Specifically, one background task can be used to monitor all processes in the system. For those processes that are idle for more than a certain amount of time, special measures should be taken. For example, if the client prompts the user that the connection time is too long, if there is no subsequent operating system, the connection will be killed automatically or the idle connection will be killed directly. Below is a detailed discussion of how to achieve automatic monitoring of user processes in the Oracle 8x environment and how to deal with connections that exceed a certain amount of idle time.

1. Identify connections in the system that exceed a certain amount of idle time

In order to realize the background task to automatically process the idle connections that exceed 1 fixed time, the first step is to identify those connections that need to be processed from all the connections to the database server, that is, to obtain the login time of each user connected to the server and the idle time after the last operation. In the Oracle system, there is a dynamic performance view, v$session, which holds various dynamic information about the current connection of the system. There are two fields LOGON_TIME and LAST_CALL_ET to get the two answers above.

l LOGON_TIME is a date-type (Date) field, which is the user's login time;

l LAST_CALL_ET is a number font (Number) field, which means the time in seconds after the user's last statement has been executed. Each time the user executes a new statement, the field is reset to 0 and the count is restarted. We can use this field to obtain the free time after the last database operation of a connected user.

SQL below the query can be connected with the current database of all users of the 1 some basic information, such as user name, the name of the state, the connection machine, operating system, in the name of the user UNIX system process, the UNIX operating system level disconnected statements, disconnected Oracle database system of statements, landing time and the last one operation to free time and so on.


In the above query, we can use the condition SUBSTR (machine, 1, 19) NOT IN (' machine name ') to mask some machines that may need to run some very time-consuming SQL statements or some other special cases. The reason for shielding these machines is that they are not processed in the automatic recognition and processing tasks in the background.

[NextPage]

2. Identify and disconnect the stored procedures for idle users

The above query statement can get some basic information about all the connected users in the system, but how to realize the system to automatically determine the idle more than 1 fixed time connection and automatically disconnect it? The Oracle system provides a mechanism called background task (Job) automatic processing. We can write a background task to execute periodically to determine if such a user connection exists, and if so, automatically disconnect it through the background task.

First, a stored procedure is created to complete the identification and disconnection of users with a fixed amount of idle time, and then a background task is added to perform the process at a fixed time (determined according to the length of idle time), so as to realize the automatic disconnection of users with more than a fixed amount of idle time in the system.

The stored procedure p_monitor is shown below, where the parameter an_nimutes is the user input parameter used to determine how long a user has been identified and disconnected for idle time. The unit is in minutes, and the default is 60 minutes, which is 1 hour. One thing to note is that this stored procedure needs to be run as an sys user. Accordingly, the background task calling the stored procedure also needs to be added as SYS.


3. Regular execution of background tasks

Finally, we add a timing task for the system, and the stored procedure created above can be called periodically to complete the system's automatic identification and processing of idle users.

The following is an example of an actual call. Under the sys user, first add a task that runs every half hour and calls the P_monitor stored procedure every time to find the connection in the system that has been idle for more than an hour and then automatically disconnect.

On 1 page