session leak

生产上遇到的一个问题，session数暴增，inactive达到4-5000，但Cursors/Session仅为几，如图所示：

1. 靠awr或者ash都查不出来。dba_hist_active_sess_history也不行，除非inactive的session被记录，但好像还没的这个表或视图。

2. 只能在出问题的时候对v$session进行监控，查看inactive的session所对应的machine、program或者username。

3. 脚本如下

sqlplus -s DB_CONN_STR@SH_DB_SID < set feed off
set verify off
set line 132
set pages 200


col username format a15
col sql_id format a20
col sql_address format a20
col machine format a30
col osuser format a15
col logon_time format a10
col program format a35
break on report
compute sum of  cnt  on report
select status,count(*) cnt from v\session group by status;
prompt .


select program,cnt,status from (select program,count(*) cnt,status from v\$session group by program,status order by cnt desc) where rownum<10;


prompt .
  select username,
      sum(cnt) total_cnt,
      sum(decode(status,'ACTIVE', cnt,0)) ACTIVE,
      sum(decode(status,'INACTIVE', cnt,0)) INACTIVE,
      sum(decode(status,'KILLED', cnt,0)) KILLED,
      sum(decode(status,'SNIPED', cnt,0)) SNIPED,issue_program1 "JDBC Thin Client",
     issue_program2  "program1",issue_program3  "program2"
     from (select program,username,status,count(*) cnt from V\$SESSION   group by program,username,status  ) 
    group by username having sum(cnt)>50 order by total_cnt desc;


EOF
exit