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. ;)
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Puddintopia

The Ramblings and Ravings Of Author Jason A. Rust

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

flashdba

Oracle databases, storage and the high-performance world of flash memory

jarneil

The thoughts of Jason Arneil

Frits Hoogland Weblog

IT Technology; Oracle, linux, TCP/IP and other stuff I find interesting

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

Martins Blog

Trying to explain complex things in simple terms

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Talip Hakan Ozturk's ORACLE BLOG

The secret of success is at your fingertips!...

Martin Widlake's Yet Another Oracle Blog

Oracle performance, Oracle statistics and VLDBs

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Oracle Scratchpad

Just another Oracle weblog

Oracle DBA Blog

Procedures, learnings and fixes encountered by a Junior DBA

Oracle ACE Director Kai Yu's Oracle Blog

Share Oracle Tips and Tricks and Related News

Online Identity & Access Management

One Stop Shop for Identity & Access Management

Uwe Hesse

about Database Technology

Oracle database internals by Riyaj

Discussions about Oracle performance tuning, RAC, Oracle internal & E-business suite.

Support

WordPress.com Support

Kamran Agayev's Oracle Blog

Oracle Certified Master

%d bloggers like this: