[20120307]檢視v$session檢視的定義.txt

lfree發表於2012-03-07
Session
[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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章