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:
- CREATE profile Killidle LIMIT idle_time 30;
- SELECT * from Dba_profiles WHERE profile= ' Killidle ';
- ALTER USER Test_user profile Killidle;
- SELECT username,profile from Dba_users WHERE username= ' Test_user ';
- ALTER SYSTEM SET resource_limit=true;
- CREATE OR REPLACE PROCEDURE sp_kill_idlesession
- /**********************************
- Clears the idle timeout session process
- **********************************/
- As
- CURSOR C_kill_sqls
- Is SELECT ' alter system kill session ' | | s.sid| | ', ' | | s.serial#| | ' immediate ' SQLSTR from v$session s WHERE s.status= ' sniped ';
- BEGIN
- For V_sql in C_kill_sqls
- LOOP
- EXECUTE IMMEDIATE V_sql.sqlstr;
- END LOOP;
- END;
- --add job to periodically clean out expired sessions
- DECLARE jobnum Number: = 661;
- BEGIN
- Dbms_job.submit (Job = Jobnum,
- what = ' sp_kill_idlesession; ',
- Next_date = To_date (' 30-04-2014 18:00:00 ', ' dd-mm-yyyy hh24:mi:ss '),
- Interval = ' sysdate + 1/144 ');
- Commit
- END;
- --If the 30-minute expiration time is too short to have an impact on database access performance, you can adjust
- ALTER profile Killidle LIMIT idle_time 30;
Resolves an issue in which Oracle 11GR2 idle connections that cause a full number of connections