• Certifications

  • Moderator at

  • Member at

  • Archives

  • May 2011
    M T W T F S S
    « Dec   Jun »
  • Recent Posts

  • Blog Stats

    • 62,627 hits
  • Enter your email address to follow this blog and receive notifications of new posts by email.

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;

Oracle Database 10g Enterprise Edition Release – Prod
PL/SQL Release – Production
CORE Production
TNS for 32-bit Windows: Version – Production
NLSRTL Version – 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
                               ,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
                               susesow, decode(s.ksusetrn,hextoraw('00'),null,s.k
                               ED'),2,'SNIPED',3,'SNIPED', 'KILLED'),decode(s.kss
                               usemnm,s.ksusetid,s.ksusepnm, decode(bitand(s.ksus
                               '), 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
                               E','NONE'),decode(s.ksusefs, 1, 'YES', 'NO'),s.ksu
                               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
                               4294967293,to_number(null), 4294967292,to_number(n
                               ull),4294967291,  to_number(null),bitand(s.ksusebl
                               ocker, 2147418112)/65536),decode(s.ksuseblocker, 4
                               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
                               .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
                               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
------------------------------ --------------------------------------------------
                               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

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

16 Responses to “Oracle Fixed(/Dynamic) views”

  1. Thanks for every other informative site. Where else may just I get that type of info written in such an ideal method? I have a mission that I am just now running on, and I’ve been at the glance out for such info.|

  2. My family always say that I am killing my time here at net, but I know I am getting know-how every day by reading thes nice content.|

  3. I’m not that much of a internet reader to be honest but your sites really nice, keep it up! I’ll go ahead and bookmark your website to come back down the road. Cheers|

  4. Great work! This is the kind of info that are meant to be shared around the net. Shame on Google for now not positioning this submit upper! Come on over and visit my website . Thank you =)|

  5. Excellent beat ! I would like to apprentice while you amend your web site, how could i subscribe for a blog website? The account aided me a acceptable deal. I had been a little bit acquainted of this your broadcast provided bright clear concept|

  6. Thanks for the auspicious writeup. It in fact was a enjoyment account it. Glance complicated to more delivered agreeable from you! By the way, how could we keep in touch?|

  7. Hi there, I enjoy reading through your article. I wanted to write a little comment to support you.|

  8. Ron Bozich said

    Awesome issues here. I am very satisfied to see your post. Thank you so much and I’m looking ahead to contact you. Will you kindly drop me a e-mail?|

  9. Quality posts is the important to interest the viewers to pay a quick visit the site, that’s what this web page is providing.|

  10. Right away I am ready to do my breakfast, when having my breakfast coming yet again to read additional news.|

  11. Right here is the right web site for anybody who wants to find out about this topic. You understand so much its almost hard to argue with you (not that I personally will need to…HaHa). You certainly put a brand new spin on a topic that’s been written about for decades. Excellent stuff, just wonderful!|

  12. You could definitely see your skills in the work you write. The world hopes for more passionate writers like you who are not afraid to mention how they believe. At all times go after your heart.|

  13. Great beat ! I wish to apprentice whilst you amend your website, how can i subscribe for a blog site? The account aided me a acceptable deal. I have been tiny bit familiar of this your broadcast provided vibrant transparent concept|

  14. Jerry Judy said

    Sweet blog! I found it while browsing on Yahoo News. Do you have any tips on how to get listed in Yahoo News? I’ve been trying for a while but I never seem to get there! Thank you|

  15. What’s up, its pleasant article on the topic of media print, we all understand media is a enormous source of facts.|

  16. Wow! After all I got a webpage from where I know how to genuinely get helpful information concerning my study and knowledge.|

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


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.


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


The thoughts of Jason Arneil

Frits Hoogland Weblog

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


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.


WordPress.com Support

Kamran Agayev's Oracle Blog

Oracle Certified Master

%d bloggers like this: