RE: x$ksppi table

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Fri, 20 Apr 2001 10:15:26 -0700
Message-ID: <F001.002EDF74.20010420100104@fatcity.com>

> -----Original Message-----
> From: Sinardy Xing [mailto:sinardyxing_at_bcsis.com]
>
> Do you know what are x$.... tables for ?
> What information inside there and the different with v$.....

You can find out the relationship between v$ views and x$ "tables" by querying against v$fixed_view_definition. (see example at end of this e-mail)

With that being said, I think you would be well-advised to use x$ tables in reusable SQL statements ONLY when the information is not available through a v$ view. For one thing, only the SYS user can query those "tables".

As other posters have mentioned, there is some information on x$ tables at Steve Adams' website ( http://www.ixora.com.au )

Finally, if you have Richard Niemiec's book "Oracle Performance Tuning" (Oracle Press), Appendix C has a cross-reference between x$ tables and GV$ views.

SQL>select view_definition
  2  from v$fixed_view_definition
  3  where view_name = 'GV$SESSION' ;

VIEW_DEFINITION

---

select inst_id,addr,indx,ksuseser,ksuudses,ksusepro, ksuudlui,ksuudlna,ksuudoct,  ksusesow, decode(ksusetrn,hextoraw('00'),null,ksusetrn), decode(ksqpswat,hextor aw('00'),null,ksqpswat), decode(bitand(ksuseidl,11),1,'ACTIVE',0, decode(bitand( ksuseflg,4096),0,'INACTIVE','CACHED'),2,'SNIPED',3,'SNIPED', 'KILLED'), decode(k sspatyp,1,'DEDICATED',2,'SHARED',3,'PSEUDO','NONE'), ksuudsid,ksuudsna,ksuseunm, ksusepid,ksusemnm,ksusetid,ksusepnm, decode(bitand(ksuseflg,19),17,'BACKGROUND', 1,'USER',2,'RECURSIVE','?'), ksusesql, ksusesqh, ksusepsq, ksusepha, ksuseapp, k suseaph,  ksuseact, ksuseach, ksusecli, ksusefix,  ksuseobj, ksusefil, ksuseblk,  ksuseslt, ksuseltm, ksusectm,  decode(bitand(ksusepfl, 16),0,'NO','YES'),  deco

de(ksuseft, 2,'SESSION', 4,'SELECT',8,'TRANSACTIONAL','NONE'), decode(ksusefm,1,
'BASIC',2,'PRECONNECT',4,'PREPARSE','NONE'), decode(ksusefs, 1, 'YES', 'NO'), ks
usegrp,  decode(bitand(ksusepfl,16),16,'ENABLED',   decode(bitand(ksusepfl,32),3

2,'FORCED','DISABLED')),  decode(bitand(ksusepfl,64),64,'FORCED',   decode(bitan d(ksusepfl,128),128,'DISABLED','ENABLED')),  decode(bitand(ksusepfl,512),512,'FO RCED',   decode(bitand(ksusepfl,256),256,'DISABLED','ENABLED'))  from x$ksuse wh ere bitand(ksspaflg,1)!=0 and bitand(ksuseflg,1)!=0

Jacques R. Kilchoër
x8816 Received on Fri Apr 20 2001 - 12:15:26 CDT