search

tanelpoder / blog-comments

Comment repo for tanelpoder.com blog
3 stars 0 forks source link

Closed database and WITH subquery | Tanel Poder Consulting #34

Open utterances-bot opened 1 year ago

utterances-bot commented 1 year ago

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 commented 1 year ago

Hello,

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

Thanks!