Closed database and WITH subquery | Tanel Poder Consulting

[utterances-bot]

Closed database and WITH subquery | Tanel Poder Consulting

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. - Linux, Oracle, SQL performance tuning and troubleshooting - consulting & training.

https://tanelpoder.com/2008/07/14/closed-database-and-with-subquery/

[yadba-elio]

Hello,

I too found this issue today... I just rewrite the subquery avoiding the "with" clause.

Thanks!