Oracle session connection number and inactive issue record

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 deadlock
SELECT * from V$locked_object;
If the query result is no rows selected, there is no deadlock in the database. Otherwise, there is a deadlock in the database.


Next, explain the status of the session:
1.active a session in this State, indicating that it is executing, is active.
The 2.killed session in this state indicates that an error has occurred, is rolling back, and, of course, is taking up 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 ways, the difference is not clear at the moment:

1. Modify Sqlnet.ora file, add expire_time=x (unit is minutes)
My Sqlnet.ora position is in D:\oracle\ora92\network\admin.

2. By Alter profile DEFAULT LIMIT Idle_time 10; Command changes, 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 automatic kill job.
CREATE OR REPLACE PROCEDURE "Kill_session" as
V_SID number;
V_serial number;
Killer Varchar2 (1000);
CURSOR Cursor_session_info is select sid,serial# from v$session where type!= ' BACKGROUND ' and status= ' INACTIVE ' and Last_ca ll_et>2700 and Username= ' Icweb ' and machine= ' orc ';
BEGIN
Open cursor_session_info;
Loop
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;
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

Oracle session connection number and inactive issue record