RE: Using x$ tables in production

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Tue, 25 Aug 2015 13:32:42 -0400
Message-ID: <008f01d0df5c$0d15eb70$2741c250$_at_rsiz.com>

Depends on what exactly you propose and your current policy on v$ views.  

For example, pick some v$ view you currently allow folks to use, turn on autotrace, and query it. (If you don't see an x$ reference try another v$.)  

Now *sometimes* the Oracle v$ view might include some complex unions and joins to deliver the general answer and be considerably more expensive to query than some subset you might need for a particular use.  

So in that case, if you define a view on the (cheaper, else why bother?) subset of the use of x$ and expose it the same way you expose the v$ views already in use then you are very unlikely to break anything. You DO pick up the responsibility to verify whether the v$ view you cribbed from to define your view changes as you might have to make a corresponding change to your "x_v$" view to make it remain true.  

If you go beyond view contents of existing v$ view you expose (or to users that currently don't get v$ view access), then you have entered the "tread carefully and it is all your own fault" zone. First, you have extended access in a way someone else might have decided was prudent. (A reasonable work-around for that is to first negotiate exposing additional v$ views to general users or document that speed and cost, not security, was the reason the v$ view was previously not exposed.) If you go beyond Oracle's use in a v$ view (as opposed to a subset) then you have also lost your clue from Oracle that you might need to change something, because you no longer have the v$ view change as a trigger to check your use of x$ in the creation of your "x_v$" view.  

So, my advice is avoid this unless you need to for a reason, and my evidence that it does not necessarily cause harm per se is that x$ tables are referenced and exposed via some v$ views already.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of McPeak, Matt
Sent: Tuesday, August 25, 2015 1:04 PM
To: oracle-l_at_freelists.org
Subject: Using x$ tables in production  

So, as a DBA and/or experienced developer of production-quality systems, would you ever:  

. Make a view on (or otherwise expose select access to) an SYS.X$*
table to a non SYS user?

. Use that view (or direct access) in an production system to drive a
process.  

My question is about using X$ tables in general. Are they OK to use (I know they can change from release to release), or is using one like logging on as SYS (i.e., just don't do it)?  

Thoughts (with supporting evidence, if possible)?  

Thanks!

Matt

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Aug 25 2015 - 19:32:42 CEST