ORA-02030 and invisible objects. The database bites back !

Being Luis Suarez’s agent must be an interesting job right now.
Maybe the man was a bit peckish.
Alternatively, maybe he’s resigned to the FA’s reluctance to introduce a mid-season break and was simply making his own arrangements for time off during the season.
Either way, this particular agent may well be trying to sign Luis up for an ad campaign for a popular brand of toothpaste.

Oracle DBA’s may sometimes have some sympathy with Suarez, although they’re more likely to end up chewing the desk in frustration, rather than their fellow DBA’s (unless the Christmas Party has really gotten out of hand).
Every so often, Oracle throws out an error that, on the face of it, makes absolutely no sense…

The user

Let’s start by creating an ordinary, every-day user – not quite a dba :

GRANT connect, resource TO nqdba IDENTIFIED BY pwd;

At this point, as you’d expect, this user doesn’t have access to very much :

SQL> desc dba_tables
ERROR:
ORA-04043: object "SYS"."DBA_TABLES" does not exist

SQL> desc v$instance
ERROR:
ORA-04043: object "SYS"."V_$INSTANCE" does not exist

It’s probably worth noting that the public synonym is being referenced for V$INSTANCE.
Although the describe is on V$INSTANCE, the error is about V_$INSTANCE. The relevance of this will become apparent shortly.

Grant Select

For now though, our hard-pressed DBA may decide to solve this problem for the user by doing the following :

GRANT SELECT ON dba_tables TO nqdba
/

Grant succeeded.

…and now for V$INSTANCE…

GRANT SELECT ON v$instance TO nqdba
                *
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

That’s interesting, the grant on DBA_TABLES works no problem. However, V$INSTANCE is having none of it.
Also, the error is on V$INSTANCE – i.e. the synonym, rather than V_$INSTANCE, the underlying object.
I wonder if there’s an object other than the synonym with this name …

SQL> SELECT owner, object_type
  2  FROM dba_objects
  3  WHERE object_name = 'V$INSTANCE' 
  4  /

OWNER			       OBJECT_TYPE
------------------------------ -------------------
PUBLIC			       SYNONYM

SQL> 

So, there’s apparently nothing apart from the synonym.
To eliminate the synonym as the cause of our problem, let’s try the following :

CREATE PUBLIC SYNONYM sparkly_white FOR v$instance
/
GRANT SELECT ON sparkly_white TO nqdba
/

Grant succeeded.

SQL>

So, granting via a synonym that’s NOT V$INSTANCE works fine. Therefore, there must be an object owned by SYS that is also called V$INSTANCE.

Them Dynamically Fixed thingys

The error states that we can only select from “fixed tables/views”. The syntax here is interesting.
V$ views are more properly referred to as Dynamic Performance Views. These views are based on what are known as fixed tables. These tables are essentially representations of C structs deep in the Oracle Kernel.

Let’s see what the V_$INSTANCE view is actually based on…

set long 5000
SELECT text
FROM dba_views
WHERE owner = 'SYS'
AND view_name = 'V_$INSTANCE';

…run this and we get…

select "INSTANCE_NUMBER","INSTANCE_NAME","HOST_NAME",
   "VERSION","STARTUP_TIME","STATUS",
    "PARALLEL","THREAD#","ARCHIVER",
    "LOG_SWITCH_WAIT","LOGINS","SHUTDOWN_PENDING",
    "DATABASE_STATUS","INSTANCE_ROLE","ACTIVE_STATE",
    "BLOCKED","EDITION" 
from v$instance

which is extremely confusing. The view is apparently pointing back to the synonym ( which after all, is the only object that we can find with that name in DBA_OBJECTS).

At this point, we give up on the conventional data dictionary views and dive into the twighlight world of V$FIXED_VIEW_DEFINITION. We should find the true view statement here, with luck…

SELECT  view_definition
FROM v$fixed_view_definition
WHERE view_name = 'V$INSTANCE'
/

Finally, we can see that there is actually an object called v$instance apart from the synonym, although this query yields the scarcely-more-helfpul…

SELECT INSTANCE_NUMBER , INSTANCE_NAME , HOST_NAME , 
    VERSION , STARTUP_TIME , STATUS , 
    PARALLEL , THREAD# , ARCHIVER , 
    LOG_SWITCH_WAIT , LOGINS , SHUTDOWN_PENDING, 
    DATABASE_STATUS, INSTANCE_ROLE, ACTIVE_STATE, 
    BLOCKED, EDITION 
FROM GV$INSTANCE 
WHERE inst_id = USERENV('Instance')

If we now perform the same check to find out what GV$INSTANCE is pointing at, the results are a bit more revealing :

select ks.inst_id,ksuxsins,ksuxssid,
    ksuxshst,ksuxsver,ksuxstim,
    decode(ksuxssts,0,'STARTED',1,'MOUNTED',2,'OPEN',3,'OPEN MIGRATE','UNKNOWN'),
    decode(ksuxsshr,0,'NO',1,'YES',2,NULL),ksuxsthr,
    decode(ksuxsarc,0,'STOPPED',1,'STARTED','FAILED'),
    decode(ksuxslsw,0,NULL,2,'ARCHIVE LOG',3,'CLEAR LOG',4,
    'CHECKPOINT', 5,'REDO GENERATION'),
    decode(ksuxsdba,0,'ALLOWED','RESTRICTED'),
    decode(ksuxsshp,0,'NO','YES'),
    decode(kvitval,0,'ACTIVE',2147483647,'SUSPENDED','INSTANCE RECOVERY'),
    decode(ksuxsrol,1,'PRIMARY_INSTANCE',2,'SECONDARY_INSTANCE','UNKNOWN'), 
    decode(qui_state,0,'NORMAL',1,'QUIESCING',2,'QUIESCED','UNKNOWN'), 
    decode(bitand(ksuxsdst, 1), 0, 'NO', 1, 'YES', 'NO'), 
    decode(ksuxsedition, 1, 'PO', 2, 'SE', 4, 'EE', 8,'XE', 'UNKNOWN') 
from x$ksuxsinst ks, x$kvit kv, x$quiesce qu 
where kvittag = 'kcbwst'

Finally, we can see that V$INSTANCE is ultimately referencing fixed tables.

The solution

The solution to this problem is simple enough…well, simple enough when you know it. Simply grant the privilege on the underlying view. So…

GRANT SELECT ON v_$instance TO nqba
/

Grant succeeded.

V$FIXED_TABLE

At this point you may be thinking that it would be really useful if there was a list of those dynamic/fixed view thingys available. Well, as you’re a proper DBA, you probably wouldn’t use the term thingys, but I know where you’re coming from…

SELECT name, type
FROM v$fixed_table
WHERE name = 'V$INSTANCE'
/

There you go. If you’re trying to grant select on any of the tables listed in V$FIXED_TABLE, you’ll more than likely hit this particular error. Additonally, if the table is listed here, it means that you should be able to still see it even if the database itself is not opened.

To demonstrate, connect to an idle instance. If you’re running XE, simply shutdown the database then issue the following command at the prompt :

sqlplus /nolog

This will enable the prompt but you won’t be connected to the database. To connect :

conn sys as sysdba

… and supply the password when prompted.
You will now get the message :

Connected to an idle instance

NOTE – an alternative way to do this on linux would be to switch to the oracle owner :

sudo su oracle
sqlplus / as sysdba

So, you’re connected to an idle instance. It hasn’t just been banned for 10 matches for being a bit bitey, it’s idle because it’s not been started.
Now mount the database ( at this point it will still not be properly started – i.e. started and open) :

startup mount

Now we can see that the database is not currently mounted, after all, you can’t query anything…

SELECT * FROM dba_tables
              *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only


SQL> 

…except for…

SQL> SELECT instance_name, status
  2  FROM v$instance;

INSTANCE_NAME	 STATUS
---------------- ------------
XE		 MOUNTED

SQL> 

Just as useful, you can still see V$FIXED_TABLE when the database is mounted ( or unmounted for that matter), so you can always check to see which tables you can query.

Incidentally, once you’ve finished playing, you can either shutdown the database again :

shutdown

…or open it…

ALTER DATABASE OPEN
/

Deb has expressed her utter bafflement as to why a footballer would bite a fellow player. Then again, that’s not too surprising…she is a vegitarian after all.

About these ads

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