Resolves an issue in which Oracle 11GR2 idle connections that cause a full number of connections

Today, I have encountered the problem of 11GR2 connection, before, because the application there is no in-depth inspection, did not find specific reasons, think of this version of the Oracle bug it.

The last approach is to use the shell script to kill v$session.status= ' INACTIVE ' connection in the system, but this site does not deploy scripts in the operating system permissions, had to do in the database processing, fortunately we have full access to the database. This time using the Profile+job timed alter system kill ' sid,seral# ' immediate way. The specific script is as follows:

2. CREATE profile Killidle LIMIT idle_time 30;
   
4. 
   
6. SELECT * from Dba_profiles WHERE profile= ' Killidle ';
   
8. 
   
10. ALTER USER Test_user profile Killidle;
    
12. 
    
14. SELECT username,profile from Dba_users WHERE username= ' Test_user ';
    
16. 
    
18. ALTER SYSTEM SET resource_limit=true;
    
20. 
    
22. 
    
24. 
    
26. 
    
28. 
    
30. 
    
32. CREATE OR REPLACE PROCEDURE sp_kill_idlesession
    
34. 
    
36. /**********************************
    
38. 
    
40. Clears the idle timeout session process
    
42. 
    
44. **********************************/
    
46. 
    
48. As
    
50. 
    
52. CURSOR C_kill_sqls
    
54. 
    
56. Is SELECT ' alter system kill session ' | | s.sid| | ', ' | | s.serial#| | ' immediate ' SQLSTR from v$session s WHERE s.status= ' sniped ';
    
58. 
    
60. BEGIN
    
62. 
    
64. For V_sql in C_kill_sqls
    
66. 
    
68. LOOP
    
70. 
    
72. EXECUTE IMMEDIATE V_sql.sqlstr;
    
74. 
    
76. END LOOP;
    
78. 
    
80. END;
    
82. 
    
84. 
    
86. 
    
88. 
    
90. 
    
92. --add job to periodically clean out expired sessions
    
94. 
    
96. DECLARE jobnum Number: = 661;
    
98. 
    
100. BEGIN
     
102. 
     
104. Dbms_job.submit (Job = Jobnum,
     
106. 
     
108. what = ' sp_kill_idlesession; ',
     
110. 
     
112. Next_date = To_date (' 30-04-2014 18:00:00 ', ' dd-mm-yyyy hh24:mi:ss '),
     
114. 
     
116. Interval = ' sysdate + 1/144 ');
     
118. 
     
120. Commit
     
122. 
     
124. END;
     
126. 
     
128. 
     
130. 
     
132. 
     
134. 
     
136. 
     
138. 
     
140. --If the 30-minute expiration time is too short to have an impact on database access performance, you can adjust
     
142. 
     
144. ALTER profile Killidle LIMIT idle_time 30;

Resolves an issue in which Oracle 11GR2 idle connections that cause a full number of connections