The Tales of Indiana Jones and Oracle x$Tables

Indeed, in this days, indiana has got a hard track to go, a DBA even more, i mention…

In this case, if we want to know about the LRU/MRU-Algorithm an d Oracles-Implementation “of them”, you can use some hidden features. For example take a look at the x$ Tables. Performance- and other Information-Views are mapped by x$ Tables. Okay, when i say tables, indiana brings out his lash… so its the half thruth. Because when we speak tables we can misunterstand this. Oracle is forcing those x$ “tables” by mapping C-Structures to them. C-Structure, huh? Yes, of course!

Next stepping is to logon as sysdba.

cmd> sqlplus tiger/scott as sysdba;
sql> select * from x$bh;

x$bh is the table to fetch information about datablocks and the usage of them by the cache.

Now if you want to use them, you have to create view and grant rights to your own:

sql>CREATE VIEW MYBH_XVIEW AS SELECT * FROM X$BH;
sql>GRANT SELECT ON MYBH_XVIEW TO SCOTT;

A list of all tables can be listed by this statement:

sql> SELECT* FROM v$fixed_table;

or

sql> SELECT * FROM v$fixed_table_definition;

Rather lists the last statement all mapping-relationships between x$NAME and v$NAME (and others)

May 22, 2008 | Filed Under Oracle, Database Competence Center

 

Comments

One Response to “The Tales of Indiana Jones and Oracle x$Tables”

  1. Jaelyn on August 11th, 2011 8:57 pm

    Wowza, prolebm solved like it never happened.

Leave a Reply




Security Code: