ORACLE INTERNALS

While recently discussing my NAEVIUS performance monitoring software for Oracle (which will be presented and released with full source code at IOUG’s Collaborate 2008), the person who I was talking to had asked how I was able to query X$ tables without being SYS; he mentioned trying to do the same thing, but running into ORA-02030 when he tried to grant SELECT access on X$ tables to another user.

If you’ve ever run into this issue, you already know how to solve it. This blog entry is intended not only to solving the problem, but also to understand the behavior and why the solution is required.

First, let’s look at what he was attempting and the ORA-02030 in detail:

SQL> GRANT SELECT ON x$ksled TO scott;
                     *
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

Obviously, this doesn’t work. So, let’s see what the Oracle docs say:

This obviously presents a problem for those who, for some crazy reason or another, wants a non-DBA to access the X$ tables. Before we start, let’s first cover what we’d ask ourselves in this situation.

Where did these godforsaken X$ tables come from?
The most hilarious explanation I’ve heard is from my good buddy Kurt Van Meerbeeck:

I believe, to truly understand X$ tables, you need to have some basic knowledge of an eastern European language … just to even pronounce them. Now, Oracle used to be named after a CIA project right? And Oracle has existed, for how long now? 31 years?

I think the Americans stole the source code from the Russians during the cold war and asked Larry and his team to read in the tape. After all, they were developing tape driver software, weren’t they? They were probably expecting some list of agents and I can imagine the CIA being quite disappointed when they saw this source code full of X$ garble.

I guess they told Larry he could keep the tapes and on he went revealing this relational database power… of course they couldn’t use the X$ stuff so they wrote V$ views on top of them; so people could understand…

It’s not a coincidence that after the fall of the Berlin wall a *lot* of features were added; Oracle 6 was almost a complete rewrite!!!

Why oh why can’t I GRANT access to X$ tables?
As noted by the error message, the X$ tables are fixed-tables. Fixed tables are not real tables! Fixed tables are defined directly in the Oracle source code and are based directly off of the C structures used in the database kernel. So, when you see an X$ table, you can generally expect it to be based on a similarly-named C structure (i.e. X$KSLEI == struct kslei).

What the bloody hell is a C structure?
If you’re not a low-level programmer, you probably haven’t run across C structures. But, if you’ve done any programming at all, you’ve likely seen or used similar structures.

In short, a structure is a collection of variables (members) under a single name. These variables can be of different types, and each has a name which is used to select it from the structure. A structure is simply a convenient way of grouping several pieces of related information together.

A structure can also be defined as a new named type, thus extending the number of available types. A structure can also use other structures, arrays, and pointers as some of its members.

The structure I’ll use as an example is ksled (listed below). I don’t know what Oracle’s actual structure looks like nor think this version completely represents the full structure; this is simply an example.

struct ksled
{
    text   *kslednam;                             /* Event Descriptor Name */
    text   *ksledp1;                                  /* Parameter Text #1 */
    text   *ksledp2;                                  /* Parameter Text #2 */
    text   *ksledp3;                                  /* Parameter Text #3 */
    ub4     ksledclassid;                                 /* Wait Class ID */
    ub4     ksledclassno;                                  /* Wait Class # */
    text   *ksledclass;                                      /* Wait Class */
    ub4     ksledhash;                              /* Event Descriptor ID */
};
typedef struct ksled ksled;

How are C structures mapped to SQL?
Good question. Let’s choose an example, the Kernel Services Lock Management Event Descriptor table, X$KSLED (whose structure we saw above).

If we look at the X$KSLED fixed table, we see the following:

So C structures don’t represent real objects?
You got it. That’s why you can’t GRANT access to them.

Is there any way to get around a direct GRANT?
Of course! The best way to do this is to create a view for each X$ table you want to GRANT access to.

SQL> CREATE VIEW v_x$ksled AS SELECT * FROM x$ksled;
SQL> GRANT SELECT ON v_x$ksled TO scott;                     *

Enough is enough! How can I quickly generate X$ table views?
Now that I’ve filled your head with nonsense, I’ll tell you that you can find an excellent X$ table view-generation script from Steve Adams at http://www.ixora.com.au/scripts/sql/create_xviews.sql

$ sqlplus sys/pwd as sysdba \
    @http://www.ixora.com.au/scripts/sql/create_xviews.sql

NOTE: I’m currently working on this one. There are still several posts which haven’t been fully copied from my static text, so bear with me :)

Trackback URI | Comments RSS