Oracle session connection number and inactive issue record

Oracle session connection number and inactive issue record

http://timnity.javaeye.com/blog/280383

Since last week, the server Oracle database problems, in less than half a day, will be reported maxsession (150) problem, it is certain that the database session exceeded the maximum number.Since the server is running file transfer applications, the requests and sessions must be very large, so the number of users has reached the maximum number of sessions for Oracle.There are two ways to deal with it: to increase the maximum number of sessions for Oracle and to clear inactive session, and, of course, from database connection pooling and program bugs.
Some statements that view the current session are collected from the rest of the page, recording:
1.select Count (*) from v$session;Select COUNT (*) from v$process;View the current total number of sessions and the number of processes, both of which are important views related to the session and process, and the information is taken from there.
2. Query the number of connections for those applications at this time?Select B.machine, B.program, COUNT (*) from v$process A, v$session b where a.addr = B.paddr and b.username are NOT null Group BY B.machine, B.program ORDER by COUNT (*) desc;
3. Find out if there is a deadlockselect * from V$locked_object; Next, explain the status of the session: 1.active a session in this State, indicating that it is executing, active. 2.killed a session in this state, indicating that an error has occurred, is rolling back, and of course, it is also occupying system resources. Another point is that the state of the killed will generally last longer, and using the Windows Tools PL/SQL developer to kill, is not useful, to use the command: Alter system kill session ' sid,serial# '; 3.inactive a session in this state is not executing, such as the SELECT statement is complete. I thought at first, as long as the inactive state of the session, is the killing, why not release it. In fact, inactive has no effect on the database itself, but if the program does not commit in a timely manner, it can result in excessive session usage. The best way to solve inactive is to set the timeout in Oracle directly, there are two methods, the difference is not clear at the moment:
1. Modify Sqlnet.ora file, add expire_time=x (in minutes)    my Sqlnet.ora position in D:\oracle\ora92 \network\admin
2. Via alter profile DEFAULT LIMIT idle_time 10; command modification, remember to restart Oracle.

===============

Another workaround:

Select a.sid,b.spid,a.serial#,a.lockwait,a.username,a.osuser,a.logon_time,a.last_call_et/3600 LAST_HOUR,A.STATUS , ' Orakill ' | | sid| | ' ' | | SPID host_command, ' alter system kill session ' | | a.sid| | ', ' | | a.serial#| | " Sql_command from v$session a,v$process B where a.paddr=b.addr and sid>6

================

An auto kill job CREATE OR REPLACE PROCEDURE "Kill_session" as         v_sid number;         v_serial number;                 killer Varchar2 (1000) ;         CURSOR Cursor_session_info is a select sid,serial# from V$session where type!= ' BACKGROUND ' and status= ' INACTIVE ' and last_call_et>2700 and Username= ' Icweb ' and machine= ' orc '; BEGIN         Open cursor_session_info;         Loops                  fetch cursor_session_info into v_sid,v_serial;                 Exit when Cursor_ Session_info%notfound;                                                                   killer:= ' alter system disconnect session ' | | v_sid| | ', ' | | v_serial| | ' post_transaction immediate ';                                                                    execute immediate killer;                                      & nbsp;   end Loop;                 Dbms_output. Put_Line (cursor_session_info%rowcount| | ' Users with idle_time>2700s has been killed! ');                 Close Cursor_session_ Info END; /

In fact, this is still a palliative solution, it is best to resolve the connection pool automatically release the Idle process problem