eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 2011Oracle技术嘉年华-OTN CHINA TOUR大会 | Blog首页 | Oracle Transparent Data Encryption - 透明数据加密 »

数据字典视图之:v$session 结构

在Oracle 11gR2中,这个视图定义超过了4000个字符,达到了4344个字符,不知道还有没有更长的了?

其定义如下:
select s.inst_id,
       s.addr,
       s.indx,
       s.ksuseser,
       s.ksuudses,
       s.ksusepro,
       s.ksuudlui,
       s.ksuudlna,
       s.ksuudoct,
       s.ksusesow,
       decode(s.ksusetrn, hextoraw('00'), null, s.ksusetrn),
       decode(s.ksqpswat, hextoraw('00'), null, s.ksqpswat),
       decode(bitand(s.ksuseidl, 11),
              1,
              'ACTIVE',
              0,
              decode(bitand(s.ksuseflg, 4096), 0, 'INACTIVE', 'CACHED'),
              2,
              'SNIPED',
              3,
              'SNIPED',
              'KILLED'),
       decode(s.ksspatyp,
              1,
              'DEDICATED',
              2,
              'SHARED',
              3,
              'PSEUDO',
              4,
              'POOLED',
              'NONE'),
       s.ksuudsid,
       s.ksuudsna,
       s.ksuseunm,
       s.ksusepid,
       s.ksusemnm,
       s.ksusemnp,
       s.ksusetid,
       s.ksusepnm,
       decode(bitand(s.ksuseflg, 19),
              17,
              'BACKGROUND',
              1,
              'USER',
              2,
              'RECURSIVE',
              '?'),
       s.ksusesql,
       s.ksusesqh,
       s.ksusesqi,
       decode(s.ksusesch, 65535, to_number(null), s.ksusesch),
       s.ksusesesta,
       decode(s.ksuseseid, 0, to_number(null), s.ksuseseid),
       s.ksusepsq,
       s.ksusepha,
       s.ksusepsi,
       decode(s.ksusepch, 65535, to_number(null), s.ksusepch),
       s.ksusepesta,
       decode(s.ksusepeid, 0, to_number(null), s.ksusepeid),
       decode(s.ksusepeo, 0, to_number(null), s.ksusepeo),
       decode(s.ksusepeo, 0, to_number(null), s.ksusepes),
       decode(s.ksusepco,
              0,
              to_number(null),
              decode(bitand(s.ksusstmbv, power(2, 11)),
                     power(2, 11),
                     s.ksusepco,
                     to_number(null))),
       decode(s.ksusepcs,
              0,
              to_number(null),
              decode(bitand(s.ksusstmbv, power(2, 11)),
                     power(2, 11),
                     s.ksusepcs,
                     to_number(null))),
       s.ksuseapp,
       s.ksuseaph,
       s.ksuseact,
       s.ksuseach,
       s.ksusecli,
       s.ksusefix,
       s.ksuseobj,
       s.ksusefil,
       s.ksuseblk,
       s.ksuseslt,
       s.ksuseorafn,
       s.ksuseltm,
       s.ksusectm,
       decode(bitand(s.ksusepxopt, 12), 0, 'NO', 'YES'),
       decode(s.ksuseft,
              2,
              'SESSION',
              4,
              'SELECT',
              8,
              'TRANSACTIONAL',
              'NONE'),
       decode(s.ksusefm, 1, 'BASIC', 2, 'PRECONNECT', 4, 'PREPARSE', 'NONE'),
       decode(s.ksusefs, 1, 'YES', 'NO'),
       s.ksusegrp,
       decode(bitand(s.ksusepxopt, 4),
              4,
              'ENABLED',
              decode(bitand(s.ksusepxopt, 8), 8, 'FORCED', 'DISABLED')),
       decode(bitand(s.ksusepxopt, 2),
              2,
              'FORCED',
              decode(bitand(s.ksusepxopt, 1), 1, 'DISABLED', 'ENABLED')),
       decode(bitand(s.ksusepxopt, 32),
              32,
              'FORCED',
              decode(bitand(s.ksusepxopt, 16), 16, 'DISABLED', 'ENABLED')),
       s.ksusecqd,
       s.ksuseclid,
       decode(s.ksuseblocker,
              4294967295,
              'UNKNOWN',
              4294967294,
              'UNKNOWN',
              4294967293,
              'UNKNOWN',
              4294967292,
              'NO HOLDER',
              4294967291,
              'NOT IN WAIT',
              'VALID'),
       decode(s.ksuseblocker,
              4294967295,
              to_number(null),
              4294967294,
              to_number(null),
              4294967293,
              to_number(null),
              4294967292,
              to_number(null),
              4294967291,
              to_number(null),
              bitand(s.ksuseblocker, 2147418112) / 65536),
       decode(s.ksuseblocker,
              4294967295,
              to_number(null),
              4294967294,
              to_number(null),
              4294967293,
              to_number(null),
              4294967292,
              to_number(null),
              4294967291,
              to_number(null),
              bitand(s.ksuseblocker, 65535)),
       decode(s.ksusefblocker,
              4294967295,
              'UNKNOWN',
              4294967294,
              'UNKNOWN',
              4294967293,
              'UNKNOWN',
              4294967292,
              'NO HOLDER',
              4294967291,
              'NOT IN WAIT',
              'VALID'),
       decode(s.ksusefblocker,
              4294967295,
              to_number(null),
              4294967294,
              to_number(null),
              4294967293,
              to_number(null),
              4294967292,
              to_number(null),
              4294967291,
              to_number(null),
              bitand(s.ksusefblocker, 2147418112) / 65536),
       decode(s.ksusefblocker,
              4294967295,
              to_number(null),
              4294967294,
              to_number(null),
              4294967293,
              to_number(null),
              4294967292,
              to_number(null),
              4294967291,
              to_number(null),
              bitand(s.ksusefblocker, 65535)),
       w.kslwtseq,
       w.kslwtevt,
       e.kslednam,
       e.ksledp1,
       w.kslwtp1,
       w.kslwtp1r,
       e.ksledp2,
       w.kslwtp2,
       w.kslwtp2r,
       e.ksledp3,
       w.kslwtp3,
       w.kslwtp3r,
       e.ksledclassid,
       e.ksledclass#,
       e.ksledclass,
       decode(w.kslwtinwait,
              0,
              decode(bitand(w.kslwtflags, 256),
                     0,
                     -2,
                     decode(round(w.kslwtstime / 10000),
                            0,
                            -1,
                            round(w.kslwtstime / 10000))),
              0),
       decode(w.kslwtinwait,
              0,
              round((w.kslwtstime + w.kslwtltime) / 1000000),
              round(w.kslwtstime / 1000000)),
       decode(w.kslwtinwait,
              1,
              'WAITING',
              decode(bitand(w.kslwtflags, 256),
                     0,
                     'WAITED UNKNOWN TIME',
                     decode(round(w.kslwtstime / 10000),
                            0,
                            'WAITED SHORT TIME',
                            'WAITED KNOWN TIME'))),
       w.kslwtstime,
       decode(w.kslwtinwait,
              0,
              to_number(null),
              decode(bitand(w.kslwtflags, 64), 64, 0, w.kslwttrem)),
       w.kslwtltime,
       s.ksusesvc,
       decode(bitand(s.ksuseflg2, 32), 32, 'ENABLED', 'DISABLED'),
       decode(bitand(s.ksuseflg2, 64), 64, 'TRUE', 'FALSE'),
       decode(bitand(s.ksuseflg2, 128), 128, 'TRUE', 'FALSE'),
       decode(bitand(s.ksuseflg2, 65536) + bitand(s.ksuseflg2, 131072),
              65536,
              'ALL EXEC',
              131072,
              'NEVER',
              0,
              'FIRST EXEC'),
       s.ksuudsae,
       s.ksusecre,
       s.ksusecsn,
       s.ksuseecid
  from x$ksuse s, x$ksled e, x$kslwt w
 where bitand(s.ksspaflg, 1) != 0
   and bitand(s.ksuseflg, 1) != 0
   and s.indx = w.kslwtsid
   and w.kslwtevt = e.indx
/

够复杂,也非常强大了。



历史上的今天...
    >> 2010-09-06文章:
    >> 2008-09-06文章:
    >> 2007-09-06文章:
           《循序渐进Oracle》勘误表
    >> 2005-09-06文章:

无觅

By eygle on 2011-09-06 12:49 | Comments (1) | FAQ | 2862 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com