Reconnect _oracle After Oracle 8x implementation is automatically disconnected

The Oracle tutorial you are looking at is: Reconnect after the Oracle 8x implementation is automatically disconnected.

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.


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.

[NextPage]

Second, identify and disconnect the idle user's stored procedures

The above query statement can get all the connected users in the system some basic situation, but how to realize the system automatically judge idle more than a certain time of the connection and automatically disconnect it? Oracle systems provide a mechanism called background task (Job) Automatic processing. We can write a background task to execute on a regular basis to determine whether there is such a user connection, and if so, it is automatically disconnected by the background task.

First create a stored procedure to complete the idle time of the user's identification and disconnect work, and then add a background task to be timed (according to the length of the idle time to determine) to implement the process, you can automatically disconnect the system idle more than a certain amount of time the user's needs.

Stored procedures P_monitor See, where the parameters an_nimutes for the user input parameters, to determine how long to identify and disconnect the user, in minutes, the default is 60 minutes, that is 1 hours. It is important to note that the stored procedure needs to be run as a SYS user. Accordingly, the background task that invokes the stored procedure also needs to be added as Sys.


Third, the background task timing execution

Finally, we add a timed task to the system, and periodically invoke the stored procedures created above, we can complete the system to automatically identify and handle the work of idle users.

Here is an example of an actual call, under the SYS user, first add a task, which runs every half hour, every time the p_monitor stored procedure is called to find the system idle time more than 1 hours of connection, and then automatically disconnected.

Previous page