Oracle Fixed(/Dynamic) views
Posted by sriram on May 4, 2011
“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. 
