Closed database and WITH subquery

Tanel Poder


Here’s an interesting issue I found when running a query using WITH subquery factoring when database was not open (it was in NOMOUNT mode in current case).

As you probably know you can query DUAL table when database is not open, but in this case the actual query is made against X$DUAL as seen below:

SQL> select * from dual;

ADDR           INDX    INST_ID DUM
-------- ---------- ---------- ---
051ED14C          0          1 X


When you have above fields when querying from DUAL then you know your database is probably not open.

So lets select something from dual:

SQL> select 'blah' x from dual;


It works.

Now lets run an equivalent query using subquery factoring:

SQL> with subquery as (select 'blah' x from dual) select * from subquery;
with subquery as (select 'blah' x from dual) select * from subquery
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only

Hmm, even though I’m really accessing the same X$DUAL table which worked ok just before, I can’t run that query.

I used to think that this kind of checking is done at database object level, so that when query would have resolved to base objects properly, Oracle would have realized it needs to access DUAL only and there is no such physical table like “subquery”. However, by now I realize that one can’t do database object level checking when database is closed as there is no means to access OBJ$ table itself. Chicken and egg problem (which is why the bootstrap segment exists btw).

Anyway, I decided to do a little test and to my surprise it worked!

SQL> with v$instance as (select 'blah' x from dual) select * from v$instance;



Note that I am not selecting from v$instance v$ view but I just name my subquery alias to string “v$instance”! And apparently Oracle query execution engine is fine with it, as long as you select from an “object” which name matches one of the hardcoded ones…

  1. Check out my 2022 online training classes in a new format!
    Advanced Oracle SQL Tuning training. Advanced Oracle Troubleshooting training, Linux Performance & Troubleshooting training: Learn when it's convenient to you and get your training questions answered even months after taking the class!
  2. Get weekly updates by email or follow Social/RSS