Automatically disconnect Oracle 8x before connection

The ORACLE tutorial is as follows: After Oracle 8x is automatically disconnected, connect again.

In actual database applications, we often encounter such a problem. After an operation is performed, the user connected to the Oracle database will no longer perform subsequent operations, however, it has not been 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 for a large database application. For example, taxation, industry and commerce, if there are a large number of database connections, one more connection consumes one more resource for the database server, if a large number of users connect to the database system but do not perform any operations, this will result in a waste of server system resources and increase server load, for users who are actually working, they cannot use the server resources to the maximum extent. In severe cases, the system performance may drop sharply.

How can we solve this problem? For Browser/Application/Server development, this problem can be avoided by setting a shared pool for the Application Server. However, for traditional two-layer architecture (Client/Server) applications, human intervention is required to avoid such resource waste. A backend task can be used to monitor all processes in the system. Special measures can be taken for idle processes that have been idle for more than a certain period of time. For example, the client prompts the user that the connection time is too long, if no subsequent operating system will automatically kill the connection or directly kill the idle connection. Next we will discuss in detail how to implement automatic monitoring of user processes in the Oracle 8x environment and how to deal with connections over a certain amount of free time.

1. Identify connections that have exceeded a certain idle time in the system

To enable background tasks to automatically process idle connections after a certain period of time, the first step is to identify the connections that need to be processed from all connections with the database server, that is, you need 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 stores various dynamic information about the current connection of the system. There are two fields LOGON_TIME and LAST_CALL_ET to get the above two answers.

L LOGON_TIME is a Date field, which is the user's logon time;

L LAST_CALL_ET is a Number field. It indicates the time (in seconds) after the last statement is executed. Each time a user executes a new statement, the field is reset to 0 and the count starts again. You can use this field to obtain the idle time after the last database operation.

The following SQL query statement can be used to obtain basic information about all users connected to the current database, such as the user name, status, name of the connected machine, and name of the user in the operating system, the process Number of the UNIX system, the disconnection statement at the UNIX operating system level, the disconnection statement of the Oracle database system, the login time, And the idle time from the last operation to the present.

IN the preceding query, we can block some machines by SUBSTR (machine, 1, 19) not in ('machine name, these machines may need to run some SQL statements that take a long time or some other machines in special circumstances. The reason for blocking these machines is that they are not processed in the background for automatic identification and processing tasks.

[NextPage]

2. Identify and disconnect the stored procedure of idle users

The preceding query statement can obtain some basic information about all connected users in the system. But how can the system automatically determine and disconnect connections that are idle for more than a certain period of time? The Oracle system provides a mechanism called automatic processing of background tasks (jobs. We can write a background task for scheduled execution to determine whether such a user connection exists. If so, the background task will be automatically disconnected.

First, create a stored procedure to identify and disconnect idle users for a certain period of time, and then add a background task to regularly execute the process based on the idle duration, you can automatically disconnect the system from idle space for more than a certain period of time.

For more information about the Stored Procedure p_monitor, see an_nimutes. The an_nimutes parameter is a user input parameter used to identify and disconnect the user. The unit is minutes. The default value is 60 minutes, that is, 1 hour. Note that the stored procedure must be run as a sys user. Correspondingly, the background tasks that call the stored procedure must also be added as SYS.

Iii. scheduled execution of background tasks

Finally, we add a scheduled task for the system and call the stored procedure created above to automatically identify and process idle users.

The following is an example of an actual call. Under the sys user, a task is first added and runs every half an hour. The P_monitor stored procedure is called each time, find the system idle time exceeds 1 hour of connection, and then automatically disconnect.

Previous Page