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

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

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

SQL> column VIEW_DEFINITION format a100SQL> 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.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'),d

这才发现在这个视图的定义不全，原来VIEW_DEFINITION仅仅保存4000个字节。

SQL> DESC GV$FIXED_VIEW_DEFINITIONName             Null?    Type---------------- -------- --------------INST_ID                   NUMBERVIEW_NAME                 VARCHAR2(30)VIEW_DEFINITION           VARCHAR2(4000)SQL> column VIEW_DEFINITION format a100SQL> 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.indxSQL> desc x$kqfvtName      Null?    Type--------- -------- --------------ADDR               RAW(8)INDX               NUMBERINST_ID            NUMBERKQFTPSEL           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: 5ax0q1md1w99pPlan Hash: 0select 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 INWAIT','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 KNOWNTIME'))),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.ksuseecidfromx$ksuse s, x$ksled e, x$kslwt w where bitand(s.ksspaflg,1)!=0 andbitand(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$SESSIONGV$STREAMS_CAPTUREV$RECOVERY_AREA_USAGEGV$ACTIVE_SESSION_HISTORYV$RMAN_BACKUP_SUBJOB_DETAILSV$BACKUP_DATAFILE_SUMMARYV$BACKUP_CONTROLFILE_SUMMARYGV$IOSTAT_FILE8 rows selected.

4.还有一些比较简单的方法:

--alter system flush shared_pool; 这个可以不执行！

select * from gv$session ;

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