Database Software

Hi,

How are you connected? local? through service?

Regards,

Franck.

I just connected as:

sqlplus / as sysdba

where $ORACLE_SID is set to the CDB instance name.

I can't reproduce the issue in vanilla 12.1.0.1.0.

Hi,

Can you show the execution plan, for example:

SQL> set autotrace trace explain

SQL> select * from cdb_data_files;

Execution Plan

----------------------------------------------------------

Plan hash value: 1439328272

---------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

---------------------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT        |          | 30000 |    11M|     1 (100)| 00:00:01 |       |       |        |      |            |

|   1 |  PX COORDINATOR         |          |       |       |            |          |       |       |        |      |            |

|   2 |   PX SEND QC (RANDOM)   | :TQ10000 | 30000 |    11M|     1 (100)| 00:00:01 |       |       |  Q1,00 | P->S | QC (RAND)  |

|   3 |    PX PARTITION LIST ALL|          | 30000 |    11M|     1 (100)| 00:00:01 |     1 |   254 |  Q1,00 | PCWC |            |

|   4 |     FIXED TABLE FULL    | X$CDBVW$ | 30000 |    11M|     1 (100)| 00:00:01 |       |       |  Q1,00 | PCWP |            |

---------------------------------------------------------------------------------------------------------------------------------

Regards,

Franck.

I have tried that on a 12.1.0.2 multitenant database and I get the same.

However, I am not sure if it is a bug or expected behavior.

Right it is the same in 12.1.0.2

I don't think it is an expected behavior. It can be very misleading for scripts that need to identify all datafiles.

I often use RMAN> report schema; which gives the right result.

In 12.2.0.2 select from CDB$VIEW() has been replaced with select from CONTAINERS() and there is probably a but with that (not showing seed).

There is a parameter in 12.1.0.2 that must be set to FALSE in order to get the same info as in 12.1.0.1:

alter system set "EXCLUDE_SEED_CDB_VIEW"=FALSE scope=both;

Run the query again at the root level:

select FILE_NAME from cdb_data_files;

Thanks for the info.

However, it does not work consistently for all views:

SQL> show parameter exclude_seed_cdb_view

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

exclude_seed_cdb_view                boolean     TRUE

SQL> select CON_ID,PDB_ID,PDB_NAME from containers(dba_pdbs);

    CON_ID     PDB_ID PDB_NAME

---------- ---------- ----------

         3          3 PDB1

        2          2 PDB$SEED

What do you mean?

As exclude_seed_cdb_view=true, I didn't expect a select from containers(dba_pdbs) to return a line with CON_ID=2

Yeah.. maybe it is just for some views, like the cdb_data_files view. We usually do not want to see the PDB$SEED pdb database files because we rarely need them.. this is just an opinion.

Except if we read it to get all database files (for backup, maintenance, etc) and then missing some of them is an issue...

Anyway, thanks for the info about exclude_seed_cdb_view. My tests here: 12.1.0.2 CDB views are now using CONTAINERS() - dbi services Blog

Hi Franck

Thanks for the reply here. I was busy so got no time to follow up my own question here.

But what I learnt was this:

BANNER

--------------------------------------------------------------------------------

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

PL/SQL Release 12.1.0.2.0 - Production

CORE    12.1.0.2.0      Production

TNS for Linux: Version 12.1.0.2.0 - Production

NLSRTL Version 12.1.0.2.0 - Production

SQL> show parameter EXCLUDE_SEED_CDB_VIEW

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

exclude_seed_cdb_view                boolean     TRUE

Note: We do see PDB$SEED even when this parameter is TRUE.

SQL> SELECT CON_ID, NAME, OPEN_MODE FROM V$CONTAINERS;

    CON_ID NAME                           OPEN_MODE

---------- ------------------------------ ----------

         1 CDB$ROOT                       READ WRITE

         2 PDB$SEED                       READ ONLY

         3 PDB1T                          MOUNTED

but CDB_DATA_FILES doesn't include the seed datafiles

SQL> SELECT CON_ID, TABLESPACE_NAME FROM CDB_DATA_FILES;

    CON_ID TABLESPACE_NAME

---------- ------------------------------

         1 SYSTEM

         1 SYSAUX

         1 UNDOTBS1

         1 USERS

When I alter the parameter EXCLUDE_SEED_CDB_VIEW to false then CDB_DATA_FILES also shows the seed files.

SQL> SELECT CON_ID, TABLESPACE_NAME FROM CDB_DATA_FILES;

    CON_ID TABLESPACE_NAME

---------- ------------------------------

         1 SYSTEM

         1 SYSAUX

         1 UNDOTBS1

         1 USERS

         2 SYSTEM

         2 SYSAUX

Thanks