Sriram Sanka – My Experiences with Databases & More

“Oracle maintains a set of virtual tables that record current database activity. These tables are called dynamic performance tables.”

Why the term “Fixed”?

Because They are fixed ..One cannot perform any DML , DDL or any operation other than “SELECT“,thats why these views/Tables are known as Fixed.

ind> select banner from v$version;

BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
PL/SQL Release 10.2.0.1.0 – Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 – Production
NLSRTL Version 10.2.0.1.0 – Production

5 rows selected.

ind> alter table x$ksmsp add sriram varchar2(30);
alter table x$ksmsp add sriram varchar2(30)
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views.

Oracle maintains some dynamic performance views which are dynamically created based on the “X$” tables.

Those are basically start with ‘V$’ and ‘GV$’ with one extra column “Instance_id”.

ind> desc v$fixed_table
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 TYPE                                               VARCHAR2(5)
 TABLE_NUM                                          NUMBER

ind> desc v$fixed_view_definition
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 VIEW_NAME                                          VARCHAR2(30)
 VIEW_DEFINITION                                    VARCHAR2(4000)

We can query these two views …to get more Info on these views

ind> select * from V$FIXED_VIEW_DEFINITION
  2  where view_name='V$SESSION'
  3  ;

VIEW_NAME                      VIEW_DEFINITION
------------------------------ --------------------------------------------------
V$SESSION                      select  SADDR , SID , SERIAL# , AUDSID , PADDR , U
                               SER# , USERNAME , COMMAND , OWNERID, TADDR , LOCKW
                               AIT , STATUS , SERVER , SCHEMA# , SCHEMANAME ,OSUS
                               ER , PROCESS , MACHINE , TERMINAL , PROGRAM , TYPE
                                , SQL_ADDRESS , SQL_HASH_VALUE, SQL_ID, SQL_CHILD
                               _NUMBER , PREV_SQL_ADDR , PREV_HASH_VALUE , PREV_S
                               QL_ID, PREV_CHILD_NUMBER , MODULE , MODULE_HASH ,
                               ACTION , ACTION_HASH , CLIENT_INFO , FIXED_TABLE_S
                               EQUENCE , ROW_WAIT_OBJ# , ROW_WAIT_FILE# , ROW_WAI
                               T_BLOCK# , ROW_WAIT_ROW# , LOGON_TIME , LAST_CALL_
                               ET , PDML_ENABLED , FAILOVER_TYPE , FAILOVER_METHO
                               D , FAILED_OVER, RESOURCE_CONSUMER_GROUP, PDML_STA
                               TUS, PDDL_STATUS, PQ_STATUS, CURRENT_QUEUE_DURATIO
                               N, CLIENT_IDENTIFIER, BLOCKING_SESSION_STATUS, BLO
                               CKING_INSTANCE,BLOCKING_SESSION,SEQ#, EVENT#,EVENT
                               ,P1TEXT,P1,P1RAW,P2TEXT,P2,P2RAW, P3TEXT,P3,P3RAW,
                               WAIT_CLASS_ID, WAIT_CLASS#,WAIT_CLASS,WAIT_TIME, S
                               ECONDS_IN_WAIT,STATE,SERVICE_NAME, SQL_TRACE, SQL_
                               TRACE_WAITS, SQL_TRACE_BINDS from GV$SESSION where
                                inst_id = USERENV('Instance')

1 row selected.

ind> select * from V$FIXED_VIEW_DEFINITION
  2  where view_name='GV$SESSION';

VIEW_NAME                      VIEW_DEFINITION
------------------------------ --------------------------------------------------
GV$SESSION                     select s.inst_id,s.addr,s.indx,s.ksuseser,s.ksuuds
                               es,s.ksusepro,s.ksuudlui,s.ksuudlna,s.ksuudoct,s.k
                               susesow, decode(s.ksusetrn,hextoraw('00'),null,s.k
                               susetrn),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.kss
                               patyp,1,'DEDICATED',2,'SHARED',3,'PSEUDO','NONE'),
                                 s.ksuudsid,s.ksuudsna,s.ksuseunm,s.ksusepid,s.ks
                               usemnm,s.ksusetid,s.ksusepnm, decode(bitand(s.ksus
                               eflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','?
                               '), s.ksusesql, s.ksusesqh, s.ksusesqi, decode(s.k
                               susesch, 65535, to_number(null), s.ksusesch),  s.k
                               susepsq, s.ksusepha, s.ksusepsi,  decode(s.ksusepc
                               h, 65535, to_number(null), s.ksusepch),  s.ksuseap
                               p, s.ksuseaph, s.ksuseact, s.ksuseach, s.ksusecli,
                                s.ksusefix, s.ksuseobj, s.ksusefil, s.ksuseblk, s
                               .ksuseslt, s.ksuseltm, s.ksusectm,decode(bitand(s.
                               ksusepxopt, 12),0,'NO','YES'),decode(s.ksuseft, 2,
                               'SESSION', 4,'SELECT',8,'TRANSACTIONAL','NONE'),de
                               code(s.ksusefm,1,'BASIC',2,'PRECONNECT',4,'PREPARS
                               E','NONE'),decode(s.ksusefs, 1, 'YES', 'NO'),s.ksu
                               segrp,decode(bitand(s.ksusepxopt,4),4,'ENABLED',de
                               code(bitand(s.ksusepxopt,8),8,'FORCED','DISABLED')
                               ),decode(bitand(s.ksusepxopt,2),2,'FORCED',decode(
                               bitand(s.ksusepxopt,1),1,'DISABLED','ENABLED')),de
                               code(bitand(s.ksusepxopt,32),32,'FORCED',decode(bi
                               tand(s.ksusepxopt,16),16,'DISABLED','ENABLED')),
                               s.ksusecqd, s.ksuseclid, decode(s.ksuseblocker,429
                               4967295,'UNKNOWN',  4294967294, 'UNKNOWN',42949672
                               93,'UNKNOWN',4294967292,'NO HOLDER',  4294967291,'
                               NOT IN WAIT','VALID'),decode(s.ksuseblocker, 42949
                               67295,to_number(null),4294967294,to_number(null),
                               4294967293,to_number(null), 4294967292,to_number(n
                               ull),4294967291,  to_number(null),bitand(s.ksusebl
                               ocker, 2147418112)/65536),decode(s.ksuseblocker, 4
                               294967295,to_number(null),4294967294,to_number(nul
                               l), 4294967293,to_number(null), 4294967292,to_numb
                               er(null),4294967291,  to_number(null),bitand(s.ksu
                               seblocker, 65535)),s.ksuseseq, s.ksuseopc,e.ksledn
                               am, e.ksledp1, s.ksusep1,s.ksusep1r,e.ksledp2, s.k
                               susep2,s.ksusep2r,e.ksledp3,s.ksusep3,s.ksusep3r,e
                               .ksledclassid,  e.ksledclass#, e.ksledclass, decod
                               e(s.ksusetim,0,0,-1,-1,-2,-2, decode(round(s.ksuse
                               tim/10000),0,-1,round(s.ksusetim/10000))), s.ksuse
                               wtm,decode(s.ksusetim, 0, 'WAITING', -2, 'WAITED U
                               NKNOWN TIME',  -1, 'WAITED SHORT TIME',   decode(r
                               ound(s.ksusetim/10000),0,'WAITED SHORT TIME','WAIT
                               ED KNOWN TIME')),s.ksusesvc, decode(bitand(s.ksuse
                               flg2,32),32,'ENABLED','DISABLED'),decode(bitand(s.
                               ksuseflg2,64),64,'TRUE','FALSE'),decode(bitand(s.k
                               suseflg2,128),128,'TRUE','FALSE')from x$ksuse s, x
                               $ksled e where bitand(s.ksspaflg,1)!=0 and bitand(
                               s.ksuseflg,1)!=0 and s.ksuseopc=e.indx

1 row selected.

ind> select * from V$FIXED_VIEW_DEFINITION
  2  where view_name='V$FIXED_VIEW_DEFINITION';

VIEW_NAME                      VIEW_DEFINITION
------------------------------ --------------------------------------------------
V$FIXED_VIEW_DEFINITION        select  VIEW_NAME , VIEW_DEFINITION from GV$FIXED_
                               VIEW_DEFINITION where inst_id = USERENV('Instance'
                               )

1 row selected.

ind> select * from V$FIXED_VIEW_DEFINITION
  2   where view_name='GV$FIXED_VIEW_DEFINITION';

VIEW_NAME                      VIEW_DEFINITION
------------------------------ --------------------------------------------------
GV$FIXED_VIEW_DEFINITION       select i.inst_id,kqfvinam,kqftpsel from x$kqfvi i,
                                x$kqfvt t where i.indx = t.indx



Conclusion:
The regular Dynamic views('V$') are created  based on 'Gv$' and these 'Gv$' views are based on 'X$' views
These are permanent tables/views.The X$ tables are generated when ever you instance started.
These tables are accessible to the sys User only.
These 'X$' are not even modifiable By the Super User 'SYS' 
Those are "FIXED"
Never ever try to expertise on these view on your production environment 
which will ruin you DB  complete. ;)