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

SQL>

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;

X
------------
blah

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;

X
------------
blah

SQL>
 

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…