Skip to Main Content
  • Questions

  • WITH Clause Doesn't Work in MOUNTED Database

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Russell.

Asked: January 12, 2026 - 9:01 pm UTC

Last updated: January 28, 2026 - 3:13 pm UTC

Version: 19.29

Viewed 1000+ times

You Asked

When querying a mounted database, an ORA-01219 occurs when querying anything besides instance views such as v$database. This is even the result when using a WITH clause that, itself, is an allowed query for a mounted database. For example:

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> select name from v$database;

NAME
---------
[REDACTED]

SQL> with dbname as (select name from v$database) select name from dbname;
with dbname as (select name from v$database) select name from dbname
                                                              *
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed
tables or views only


This is just a simple example. We have a larger query that would be perfectly legal for a mounted database except for this business about the WITH clause. Why couldn't this be sorted out by the optimizer so that WITH clauses would work in mounted databases? It seems to be an unnecessary restriction.

Cheers,
Russ

and Connor said...

I suspect the problem here is that a WITH *may* want to dump intermediate results to a temporary table., which would be impossible when the database is mounted.

That results in a scenario where the success of a query execution becomes non-deterministic depending on the execution plan chosen, so probably safer just to block it.


Rating

  (2 ratings)

Comments

Well, yes, but...

Russell Cannon, January 23, 2026 - 3:25 pm UTC

Wouldn't that also be a potential problem when the same selects are just inline subqueries? Is there something special about the with clause that makes it more problematic? Some extra complexity with WITH?

Cheers,
Russ
Chris Saxon
January 28, 2026 - 3:13 pm UTC

See Rajesh's comment below.

WITH Clause Vs inline-subqueries.

Rajeshwaran Jeyabal, January 25, 2026 - 5:12 am UTC

The WITH clause (subquery factoring) allows the optimizer to materialize the result set into a temporary structure when the same subquery block is referenced multiple times in a query (for example, RW1 used more than once).

This behavior is not guaranteed with inline subqueries, which are typically merged into the main query block and re-evaluated as part of the execution plan.

Because of this, as Connor mentioned, a WITH clause may attempt to dump intermediate results into a temporary table. Such an operation would be impossible when the database is only mounted (and not open), since temporary segments cannot be created in that state.

demo@ORA26AI> create table stage as select * from all_objects ;

Table created.

demo@ORA26AI> set autotrace traceonly exp
demo@ORA26AI> with rw1 as ( select owner, object_type , max(created) max_created, sum(object_id) sum_object_id
  2     from stage
  3     group by owner, object_type )
  4  , rw2 as ( select owner, max(sum_object_id) max_sum_object_id
  5             from rw1
  6             group by owner )
  7  select *
  8  from rw1, rw2
  9  where rw1.owner = rw2.owner
 10  /

Execution Plan
----------------------------------------------------------
Plan hash value: 3469050691

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                            |   370 | 66600 |   469  (14)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION               |                            |       |       |            |          |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6622_101C98F |       |       |            |          |
|   3 |    HASH GROUP BY                         |                            |   370 | 10360 |   464  (14)| 00:00:01 |
|   4 |     TABLE ACCESS FULL                    | STAGE                      | 65175 |  1782K|   423   (6)| 00:00:01 |
|*  5 |   HASH JOIN                              |                            |   370 | 66600 |     6  (34)| 00:00:01 |
|   6 |    VIEW                                  |                            |    18 |  1422 |     3  (34)| 00:00:01 |
|   7 |     HASH GROUP BY                        |                            |    18 |   324 |     3  (34)| 00:00:01 |
|   8 |      VIEW                                |                            |   370 |  6660 |     2   (0)| 00:00:01 |
|   9 |       TABLE ACCESS FULL                  | SYS_TEMP_0FD9D6622_101C98F |   370 | 10360 |     2   (0)| 00:00:01 |
|  10 |    VIEW                                  |                            |   370 | 37370 |     2   (0)| 00:00:01 |
|  11 |     TABLE ACCESS FULL                    | SYS_TEMP_0FD9D6622_101C98F |   370 | 10360 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("RW1"."OWNER"="RW2"."OWNER")

demo@ORA26AI> select *
  2  from ( select owner, object_type , max(created) max_created, sum(object_id) sum_object_id
  3     from stage
  4     group by owner, object_type ) rw1
  5     , ( select owner, max(sum_object_id) max_sum_object_id
  6             from (  select owner, object_type , max(created) max_created
  7                             , sum(object_id) sum_object_id
  8                             from stage
  9                             group by owner, object_type )
 10                             group by owner ) rw2
 11  where rw1.owner = rw2.owner;

Execution Plan
----------------------------------------------------------
Plan hash value: 2175861137

--------------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |   370 | 66600 |   928  (14)| 00:00:01 |
|*  1 |  HASH JOIN             |       |   370 | 66600 |   928  (14)| 00:00:01 |
|   2 |   VIEW                 |       |    18 |  1422 |   464  (14)| 00:00:01 |
|   3 |    HASH GROUP BY       |       |    18 |   324 |   464  (14)| 00:00:01 |
|   4 |     VIEW               |       |   370 |  6660 |   464  (14)| 00:00:01 |
|   5 |      HASH GROUP BY     |       |   370 | 10360 |   464  (14)| 00:00:01 |
|   6 |       TABLE ACCESS FULL| STAGE | 65175 |  1782K|   423   (6)| 00:00:01 |
|   7 |   VIEW                 |       |   370 | 37370 |   464  (14)| 00:00:01 |
|   8 |    HASH GROUP BY       |       |   370 | 10360 |   464  (14)| 00:00:01 |
|   9 |     TABLE ACCESS FULL  | STAGE | 65175 |  1782K|   423   (6)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("RW1"."OWNER"="RW2"."OWNER")

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.