Oracle Fixed(/Dynamic) views
Posted by Sriram Sanka 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. ;)
Esteban Delgardo said
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.|
LikeLike
Marnie Doffing said
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.|
LikeLike
Candis Kumro said
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|
LikeLike
Diego Litteral said
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 =)|
LikeLike
Bryan Cudney said
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|
LikeLike
Jason Wengerd said
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?|
LikeLike
Merna Joulwan said
Hi there, I enjoy reading through your article. I wanted to write a little comment to support you.|
LikeLike
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?|
LikeLike
Sirena Roszales said
Quality posts is the important to interest the viewers to pay a quick visit the site, that’s what this web page is providing.|
LikeLike
Vanesa Geren said
Right away I am ready to do my breakfast, when having my breakfast coming yet again to read additional news.|
LikeLike
Miguel Sharrard said
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!|
LikeLike
Lilla Magsayo said
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.|
LikeLike
Aletha Kiniry said
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|
LikeLike
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|
LikeLike
Pablo Kundtz said
What’s up, its pleasant article on the topic of media print, we all understand media is a enormous source of facts.|
LikeLike
Kerrie Sitar said
Wow! After all I got a webpage from where I know how to genuinely get helpful information concerning my study and knowledge.|
LikeLike