[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很快能找到定义的视图。
时间： 2024-09-20 16:52:40