认识v$fixed_view_definition

认识v$fixed_view_definition 

v$fixed_view_definition 这个视图功能很强，可以将一些视图的数据来源（视图的定义）给找出来。直接举例：

 

1.v$session。

select * from v$fixed_view_definition a where a.VIEW_NAME='V$SESSION';

 

VIEW_NAME                      VIEW_DEFINITION
------------------------------ --------------------------------------------------------------------------------
V$SESSION                      select  SADDR , SID , SERIAL# , AUDSID , PADDR , USER# , USERNAME , COMMAND , OWNERID, TADDR , LOCKWAIT , STATUS , SERVER , SCHEMA# , SCHEMANAME ,OSUSER , PROCESS , MACHINE , TERMINAL , PROGRAM , TYPE , SQL_ADDRESS , SQL_HASH_VALUE , PREV_SQL_ADDR , PREV_HASH_VALUE , MODULE , MODULE_HASH , ACTION , ACTION_HASH , CLIENT_INFO , FIXED_TABLE_SEQUENCE , ROW_WAIT_OBJ# , ROW_WAIT_FILE# , ROW_WAIT_BLOCK# , ROW_WAIT_ROW# , LOGON_TIME , LAST_CALL_ET , PDML_ENABLED , FAILOVER_TYPE , FAILOVER_METHOD , FAILED_OVER, RESOURCE_CONSUMER_GROUP, PDML_STATUS, PDDL_STATUS, PQ_STATUS, CURRENT_QUEUE_DURATION, CLIENT_IDENTIFIER from GV$SESSION where inst_id = USERENV('Instance')

 

--来源于GV$SESSION

 

2.继续追踪。

select * from v$fixed_view_definition a where a.VIEW_NAME='GV$SESSION';

 

VIEW_NAME                      VIEW_DEFINITION
------------------------------ --------------------------------------------------------------------------------
GV$SESSION                    select inst_id,addr,indx,ksuseser,ksuudses,ksusepro, ksuudlui,ksuudlna,ksuudoct, ksusesow, decode(ksusetrn,hextoraw('00'),null,ksusetrn), decode(ksqpswat,hextoraw('00'),null,ksqpswat), decode(bitand(ksuseidl,11),1,'ACTIVE',0, decode(bitand(ksuseflg,4096),0,'INACTIVE','CACHED'),2,'SNIPED',3,'SNIPED', 'KILLED'), decode(ksspatyp,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, ksuseaph,  ksuseact, ksuseach, ksusecli, ksusefix,  ksuseobj, ksusefil, ksuseblk, ksuseslt, ksuseltm, ksusectm,  decode(bitand(ksusepfl, 16),0,'NO','YES'),  decode(ksuseft, 2,'SESSION', 4,'SELECT',8,'TRANSACTIONAL','NONE'), decode(ksusefm,1,'BASIC',2,'PRECONNECT',4,'PREPARSE','NONE'), decode(ksusefs, 1, 'YES', 'NO'), ksusegrp,  decode(bitand(ksusepfl,16),16,'ENABLED',   decode(bitand(ksusepfl,32),32,'FORCED','DISABLED')),  decode(bitand(ksusepfl,64),64,'FORCED',   decode(bitand(ksusepfl,128),128,'DISABLED','ENABLED')),  decode(bitand(ksusepfl,512),512,'FORCED',   decode(bitand(ksusepfl,256),256,'DISABLED','ENABLED')),  ksusecqd, ksuseclid  fromx$ksuse where bitand(ksspaflg,1)!=0 and bitand(ksuseflg,1)!=0

 

--最终来源于x$ksuse

 

3.能否再深入？

SQL> select * from v$fixed_view_definition a where a.VIEW_NAME='x$ksuse';

 

VIEW_NAME                      VIEW_DEFINITION
------------------------------ ---------------------------------------------

 

--x$类型的表已经是oracle的底层表它在数据库启动的过程中动态创建，且不允许SYSDBA之外的用户访问。