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…
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.
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 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.
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 :
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) :
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>
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 :
…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.