ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20120307]查看v$session视图的定义.txt

[20120307]查看v$session视图的定义.txt

原创 Linux操作系统 作者:lfree 时间:2012-03-07 17:30:21 0 删除 编辑
[20120307]查看v$session视图的定义.txt

今天查看v$session的原始定义,查询GV$FIXED_VIEW_DEFINITION


SQL> column VIEW_DEFINITION format a100
SQL> select * from GV$FIXED_VIEW_DEFINITION where view_name='GV$SESSION';
   INST_ID VIEW_NAME                      VIEW_DEFINITION
---------- ------------------------------ ----------------------------------------------------------------------------------------------------
         1 GV$SESSION                     select s.inst_id,s.addr,s.indx,s.ksuseser,s.ksuudses,s.ksusepro,s.ksuudlui,s.ksuudlna,s.ksuudoct,s.k
                                          susesow, decode(s.ksusetrn,hextoraw('00'),null,s.ksusetrn),decode(s.ksqpswat,hextoraw('00'),null,s.k
                                          sqpswat),decode(bitand(s.ksuseidl,11),1,'ACTIVE',0,decode(bitand(s.ksuseflg,4096),0,'INACTIVE','CACH
                                          ED'),2,'SNIPED',3,'SNIPED', 'KILLED'),decode(s.ksspatyp,1,'DEDICATED',2,'SHARED',3,'PSEUDO',4,'POOLE
                                          D','NONE'),  s.ksuudsid,s.ksuudsna,s.ksuseunm,s.ksusepid, s.ksusemnm,s.ksusemnp,s.ksusetid,s.ksusepn
                                          m, 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.ksuses
                                          eid, 0, to_number(null), s.ksuseseid),  s.ksusepsq, s.ksusepha, s.ksusepsi,  decode(s.ksusepch, 6553
                                          5, 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),         decod
                                          e(bitand(s.ksusstmbv, power(2,11)), power(2,11), s.ksusepcs,                to_number(null))),  s.ks
                                          useapp, s.ksuseaph, s.ksuseact, s.ksuseach, s.ksusecli, s.ksusefix, s.ksuseobj, s.ksusefil, s.ksuseb
                                          lk, 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,'PR
                                          ECONNECT',4,'PREPARSE','NONE'),decode(s.ksusefs, 1, 'YES', 'NO'),s.ksusegrp,decode(bitand(s.ksusepxo
                                          pt,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),3
                                          2,'FORCED',decode(bitand(s.ksusepxopt,16),16,'DISABLED','ENABLED')),  s.ksusecqd, s.ksuseclid,  deco
                                          de(s.ksuseblocker,4294967295,'UNKNOWN',  4294967294, 'UNKNOWN',4294967293,'UNKNOWN',4294967292,'NO H
                                          OLDER',  4294967291,'NOT IN WAIT','VALID'),decode(s.ksuseblocker, 4294967295,to_number(null),4294967
                                          294,to_number(null), 4294967293,to_number(null), 4294967292,to_number(null),4294967291,  to_number(n
                                          ull),bitand(s.ksuseblocker, 2147418112)/65536),decode(s.ksuseblocker, 4294967295,to_number(null),429
                                          4967294,to_number(null), 4294967293,to_number(null), 4294967292,to_number(null),4294967291,  to_numb
                                          er(null),bitand(s.ksuseblocker, 65535)),  decode(s.ksusefblocker,4294967295,'UNKNOWN',  4294967294,
                                          'UNKNOWN',4294967293,'UNKNOWN',4294967292,'NO HOLDER',  4294967291,'NOT IN WAIT','VALID'),decode(s.k
                                          susefblocker,4294967295,to_number(null),4294967294,to_number(null), 4294967293,to_number(null), 4294
                                          967292,to_number(null),4294967291,  to_number(null),bitand(s.ksusefblocker, 2147418112)/65536),decod
                                          e(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.kslwtse
                                          q,w.kslwtevt,e.kslednam,e.ksledp1,w.kslwtp1,w.kslwtp1r, e.ksledp2,w.kslwtp2,w.kslwtp2r,e.ksledp3,w.k
                                          slwtp3,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.k
                                          slwtinwait,0,round((w.kslwtstime+w.kslwtltime)/1000000),  round(w.kslwtstime/1000000)), decode(w.ksl
                                          wtinwait,1,'WAITING',  decode(bitand(w.kslwtflags,256),0,'WAITED UNKNOWN TIME',   decode(round(w.ksl
                                          wtstime/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, decod
                                          e(bitand(s.ksuseflg2,32),32,'ENABLED','DISABLED'),decode(bitand(s.ksuseflg2,64),64,'TRUE','FALSE'),d


这才发现在这个视图的定义不全,原来VIEW_DEFINITION仅仅保存4000个字节。
SQL> DESC GV$FIXED_VIEW_DEFINITION
Name             Null?    Type
---------------- -------- --------------
INST_ID                   NUMBER
VIEW_NAME                 VARCHAR2(30)
VIEW_DEFINITION           VARCHAR2(4000)

SQL> column VIEW_DEFINITION format a100
SQL> select * from GV$FIXED_VIEW_DEFINITION where view_name='GV$FIXED_VIEW_DEFINITION';
   INST_ID VIEW_NAME                      VIEW_DEFINITION
---------- ------------------------------ ----------------------------------------------------------------------------------------------------
         1 GV$FIXED_VIEW_DEFINITION       select i.inst_id,kqfvinam,kqftpsel from x$kqfvi i, x$kqfvt t where i.indx = t.indx

SQL> desc x$kqfvt
Name      Null?    Type
--------- -------- --------------
ADDR               RAW(8)
INDX               NUMBER
INST_ID            NUMBER
KQFTPSEL           VARCHAR2(4000)

--昏!通过这个没有办法获得完整的定义。
--如何获得完整的定义呢?

执行如下命令:

alter system flush shared_pool;
alter session set events '10053 trace name context forever, level 1';
select * from gv$session ;
alter session set events '10053 trace name context off';

alter system flush shared_pool;
alter session set events '10046 trace name context forever, level 12';
select * from gv$session ;
alter session set events '10046 trace name context off';

$ tkprof test_ora_24079.trc aaa.txt

查看aaa.txt文件:

SQL ID: 5ax0q1md1w99p
Plan Hash: 0
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


我查询
SELECT view_name  FROM v$fixed_view_definition WHERE LENGTH (view_definition) >= 4000;
发现有几个视图定义等于4000的,也许会不完整。

SQL> SELECT view_name  FROM v$fixed_view_definition WHERE LENGTH (view_definition) >= 4000;
VIEW_NAME
------------------------------
GV$SESSION
GV$STREAMS_CAPTURE
V$RECOVERY_AREA_USAGE
GV$ACTIVE_SESSION_HISTORY
V$RMAN_BACKUP_SUBJOB_DETAILS
V$BACKUP_DATAFILE_SUMMARY
V$BACKUP_CONTROLFILE_SUMMARY
GV$IOSTAT_FILE

8 rows selected.


4.还有一些比较简单的方法:
--alter system flush shared_pool; 这个可以不执行!
select * from gv$session ;

利用toad的SGA trace查询特定的字符,比如s.ksuseser很快能找到定义的视图。


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

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2323
  • 访问量
    6050506