ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > processes 不够用了 ，用户登陆不了

processes 不够用了 ，用户登陆不了

原创 Oracle 作者：哎呀我的天呐 时间：2015-07-17 18:11:51 0 删除 编辑
数据库processes是2000 
select count(*) from v$session;
--1997
起初os层面 kill -9 select spid from $process where paddr in(select addr from v$session where username ='BI_SJJS' and status = 'INACTIVE');

什么是inactive的会话呢？
我测试了一下当一个会话刚执行sql的那一小段时间内会话是active的

点击(此处)折叠或打开

  1. select sid,serial#,status,to_char(logon_time,'yyyy-dd-mm hh24:mi:ss') "Time Of Logon" ,to_char(sysdate - last_call_et/3600/24,'YYYYMMDD HH24:MI:SS' ) "Last Call" , username from v$session

  3.        SID SERIAL# STATUS       Time Of Logon         Last Call    USERNAME
  4. ---------- ---------- -------- ------------------- --------------------------
  5.      1        5    ACTIVE 2015-11-07 01:08:05 20150711 02:48:15    SYS
  6.      2        1    ACTIVE 2015-11-07 01:07:59 20150711 01:07:59
  7.      3        1    ACTIVE 2015-11-07 01:07:59 20150711 01:07:59
  8.      4        1    ACTIVE 2015-11-07 01:08:00 20150711 01:07:59
  9.      5        1    ACTIVE 2015-11-07 01:08:00 20150711 01:08:01
  10.      6        1    ACTIVE 2015-11-07 01:08:00 20150711 01:08:01
  11.      7        1    ACTIVE 2015-11-07 01:08:00 20150711 01:08:01
  12.      8        1    ACTIVE 2015-11-07 01:08:00 20150711 01:08:01
  13.      9        1    ACTIVE 2015-11-07 01:08:00 20150711 01:08:01
  14.     10        1    ACTIVE 2015-11-07 01:08:00 20150711 01:08:01
  15.     11        1    ACTIVE 2015-11-07 01:08:00 20150711 01:08:01
  16.     12        1    ACTIVE 2015-11-07 01:08:00 20150711 01:08:01
  17.     13        1    ACTIVE 2015-11-07 01:08:00 20150711 01:08:01
  18.     14        1    ACTIVE 2015-11-07 01:08:00 20150711 01:08:01
  19.     15        1    ACTIVE 2015-11-07 01:08:00 20150711 01:08:01
  20.     16        1    ACTIVE 2015-11-07 01:08:00 20150711 01:08:01
  21.     18        5    ACTIVE 2015-11-07 01:08:05 20150711 01:08:05
  22.     20        3    ACTIVE 2015-11-07 01:08:06 20150711 01:08:06
  23.     21        1    ACTIVE 2015-11-07 01:08:06 20150711 01:08:06
  24.     23        1    ACTIVE 2015-11-07 01:08:06 20150711 01:08:06
  25.     25        1    ACTIVE 2015-11-07 01:08:07 20150711 01:08:07
  26.     26        3    ACTIVE 2015-11-07 01:08:17 20150711 01:08:17
  27.     28        5    ACTIVE 2015-11-07 01:08:08 20150711 01:08:08
  28.     30        3    ACTIVE 2015-11-07 01:08:17 20150711 01:08:17
  29.     33        1391 ACTIVE 2015-11-07 02:38:39 20150711 02:48:15    SCOTT
  30.     34        35   ACTIVE 2015-11-07 01:13:07 20150711 01:13:08
  31.     36        345  ACTIVE 2015-11-07 02:43:17 20150711 02:43:18
  32.     37        63   ACTIVE 2015-11-07 02:23:14 20150711 02:23:15

  34. 28 rows selected.
然后后下次查询的时候就变为inactive了。然后我查询了一下官方文档。查询当前等待是  SQL*NET message from client  说明sql已经执行完毕，也已经fetch了数据。所以不是active的了。另外等待IO的时候，也可能是inactive 

  • ACTIVE - Session currently executing SQL

  • INACTIVE - Session which is inactive and either has no configured limits or has not yet exceeded the configured limits

    翻译一下，说的也很郁闷啊【会话是无效的，或者没有配置的限制，或者还没有超过设定的限制】
    后续我抓了个10046，看看在数据库层面有什么问题。
    =====================
    PARSING IN CURSOR #1 len=314 dep=0 uid=66 oct=3 lid=66 tim=275802715921 hv=1860168664 ad='706a0850'
    select case when FILENAME_='/' then PARENTDIR_ else  CONCAT(PARENTDIR_,FILENAME_) end , PARENTDIR_,FILENAME_,ISFILE_,CREATETIME_,MODIFYTIME_,OWNER_,MENDER_,SIZE_,CONTENT_,NAME_,CAPTION_,EXTTYPE_,VERSION_,ISVALID_,CHARSET_,MIMETYPE_,INFO_,CALCPARAM_ from EBI_TSK33_RPT_SSFX where ISFILE_='0'
    END OF STMT


    PARSE #1:c=0,e=3178,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=275802715918
    BINDS #1:
    EXEC #1:c=0,e=69,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=275802716094
    WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1952673792 #bytes=1 p3=0 obj#=1572589 tim=275802716146
    WAIT #1: nam='SQL*Net message from client' ela= 936 driver id=1952673792 #bytes=1 p3=0 obj#=1572589 tim=275802717229
    WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1952673792 #bytes=1 p3=0 obj#=1572589 tim=275802717404
    FETCH #1:c=0,e=359,p=0,cr=18,cu=0,mis=0,r=10,dep=0,og=1,tim=275802717626
    WAIT #1: nam='SQL*Net message from client' ela= 1108 driver id=1952673792 #bytes=1 p3=0 obj#=1572589 tim=275802718771
    WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1952673792 #bytes=1 p3=0 obj#=1572589 tim=275802718826
    WAIT #0: nam='SQL*Net message from client' ela= 908 driver id=1952673792 #bytes=1 p3=0 obj#=1572589 tim=275802719759
    WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1952673792 #bytes=1 p3=0 obj#=1572589 tim=275802719798
    WAIT #0: nam='SQL*Net message from client' ela= 900 driver id=1952673792 #bytes=1 p3=0 obj#=1572589 tim=275802720722
    WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1952673792 #bytes=1 p3=0 obj#=1572589 tim=275802720791
    WAIT #0: nam='SQL*Net message from client' ela= 698 driver id=1952673792 #bytes=1 p3=0 obj#=1572589 tim=275802721513
    WAIT #0: nam='SQL*Net message to client' ela= 0 driver id=1952673792 #bytes=1 p3=0 obj#=1572589 tim=275802721547
    WAIT #0: nam='SQL*Net message from client' ela= 818 driver id=1952673792 #bytes=1 p3=0 obj#=1572589 tim=275802722389
    WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1952673792 #bytes=1 p3=0 obj#=1572589 tim=275802722424
    WAIT #0: nam='SQL*Net message from client' ela= 742 driver id=1952673792 #bytes=1 p3=0 obj#=1572589 tim=275802723190
    WAIT #0: nam='SQL*Net message to client' ela= 0 driver id=1952673792 #bytes=1 p3=0 obj#=1572589 tim=275802723225
    WAIT #0: nam='SQL*Net message from client' ela= 1080 driver id=1952673792 #bytes=1 p3=0 obj#=1572589 tim=275802724328
    WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1952673792 #bytes=1 p3=0 obj#=1572589 tim=275802724364
    WAIT #0: nam='SQL*Net message from client' ela= 914 driver id=1952673792 #bytes=1 p3=0 obj#=1572589 tim=275802725302
    WAIT #0: nam='SQL*Net message to client' ela= 0 driver id=1952673792 #bytes=1 p3=0 obj#=1572589 tim=275802725337
    WAIT #0: nam='SQL*Net message from client' ela= 913 driver id=1952673792 #bytes=1 p3=0 obj#=1572589 tim=275802726274
    WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1952673792 #bytes=1 p3=0 obj#=1572589 tim=275802726344
    FETCH #1:c=0,e=296,p=0,cr=29,cu=0,mis=0,r=5,dep=0,og=1,tim=275802726597
    WAIT #1: nam='SQL*Net message from client' ela= 792 driver id=1952673792 #bytes=1 p3=0 obj#=1572589 tim=275802727458
    WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1952673792 #bytes=1 p3=0 obj#=1572589 tim=275802727503
    WAIT #0: nam='SQL*Net message from client' ela= 705 driver id=1952673792 #bytes=1 p3=0 obj#=1572589 tim=275802728232
    WAIT #0: nam='SQL*Net message to client' ela= 0 driver id=1952673792 #bytes=1 p3=0 obj#=1572589 tim=275802728268
    WAIT #0: nam='SQL*Net message from client' ela= 716 driver id=1952673792 #bytes=1 p3=0 obj#=1572589 tim=275802729008
    WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1952673792 #bytes=1 p3=0 obj#=1572589 tim=275802729043
    WAIT #0: nam='SQL*Net message from client' ela= 624 driver id=1952673792 #bytes=1 p3=0 obj#=1572589 tim=275802729691
    WAIT #0: nam='SQL*Net message to client' ela= 0 driver id=1952673792 #bytes=1 p3=0 obj#=1572589 tim=275802729726
    WAIT #0: nam='SQL*Net message from client' ela= 847 driver id=1952673792 #bytes=1 p3=0 obj#=1572589 tim=275802730596
    STAT #1 id=1 cnt=15 pid=0 pos=1 obj=1572586 op='TABLE ACCESS FULL EBI_TSK33_RPT_SSFX (cr=47 pr=0 pw=0 time=158 us)'
    XCTEND rlbk=0, rd_only=1
    WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1952673792 #bytes=1 p3=0 obj#=1572589 tim=275802730753
    WAIT #0: nam='SQL*Net message from client' ela= 4486838 driver id=1952673792 #bytes=1 p3=0 obj#=1572589 tim=275807217626
    =====================
    PARSING IN CURSOR #1 len=18 dep=0 uid=66 oct=3 lid=66 tim=275807217907 hv=2866845384 ad='70ce6978'
    select 1 from dual
    END OF STMT
    PARSE #1:c=0,e=115,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=275807217903
    BINDS #1:
    EXEC #1:c=0,e=86,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=275807218074
    WAIT #1: nam='SQL*Net message to client' ela= 3 driver id=1952673792 #bytes=1 p3=0 obj#=1572589 tim=275807218127
    WAIT #1: nam='SQL*Net message from client' ela= 466 driver id=1952673792 #bytes=1 p3=0 obj#=1572589 tim=275807218665
    WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1952673792 #bytes=1 p3=0 obj#=1572589 tim=275807218713
    FETCH #1:c=0,e=41,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=275807218736
    WAIT #1: nam='SQL*Net message from client' ela= 833 driver id=1952673792 #bytes=1 p3=0 obj#=1572589 tim=275807219633
    STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='FAST DUAL  (cr=0 pr=0 pw=0 time=5 us)'
    =====================

    网络等待也多，，，看看程序有没有什么问题，哎呀  看java去了。。。。

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29990276/viewspace-1736566/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： Fatal NI connect error 12170.报错处理
下一篇： 11g与12c视图对比
请登录后发表评论 登录
全部评论
哎呀我的天呐
专家姓名：姚崇
https://cloud.tencent.com/developer/inventory/14362

注册时间：2014-10-30

  • 博文量
    403
  • 访问量
    2528803

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593