Implement automatic disconnection and reconnection in Oracle 8x

The following articles mainly describe how to implement the actual operation scheme of automatically disconnecting and then connecting in Oracle 8x, which involves identifying connections that exceed a certain idle time in the system, it also identifies and disconnects the stored procedures of idle users, and describes related content.

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 beyond a certain period of time, the first step is to identify the connections that need to be processed from all Oracle 8x with the database server before connecting, 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.

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 the Stored Procedure p_monitor, see the following figure. The parameter an_nimutes is the user input parameter used to determine the idle time for automatic disconnection in Oracle 8x. 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.

 
 

  
  
CREATE OR REPLACE PROCEDURE P_MONITOR(   
  
  
AN_MINUTES NUMBER DEFAULT 60)   
 
 

Usage of the stored procedure: identifies that the system has exceeded a certain idle connection time (
AS_MINUTES), and kill the parameter:
AN_MINUTES idle time, in minutes. The default value is 60 minutes.
 

1. AS
2. V_Str VARCHAR2 (100 );
3. CURSOR C_users (v_minutes number) is select s. username,
4. S. status, s. machine, 'alter system kill session'
5. | ''' | S. sid | ',' | s. serial # | ''' operates
6. FROM v $ session s, v $ process p
7. Where type = 'user'
8. AND p. addr = s. paddr
9. AND status! = 'Killed'
10. -- And substr (machine, 1, 19) not in ('machine name NOT to be processed ')
11. AND last_call_et>V_minutes * 60
12. Order by last_call_et desc;
13. BEGIN
14. FOR T_users IN C_users (an_minutes) LOOP
15. V_Str: = T_USERS.OPERATES;
16. Execute immediate v_str;
17. End loop;
18. END;
19. /

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 out the Oracle 8x that has been idle for more than one hour in the system, and then connect and then disconnect automatically.

 
 

  
  
DECLARE
  
  
Jobno number;
  
  
BEGIN
  
  
DBMS_JOB.SUBMIT (
  
  
Job => jobno,
  
  
What => 'P _ monitor (60 );',
  
  
Next_date => SYSDATE,
  
  
Interval => '/* 1: Hr */sysdate + 30/1440); -- run every half hour
  
  
END;
  
  
/
 
 

The above content is an introduction to how to enable automatic disconnection in Oracle 8x and then connect again.