Question: How to get PDB Datafile information when PDB in different Mode?

Case 1 – PDB in mounted Mode

PDB Status:

SQL> select pdb_id, pdb_name, con_uid,status from dba_pdbs;

PDB_ID      PDB_NAME        CON_UID     STATUS
----------  --------------  ----------  -------------
3           [PDB12C]        515133920   NORMAL
2           PDB$SEED        4105546291  NORMAL
SQL> select con_id, name, open_mode, restricted from v$pdbs;

CON_ID       NAME                OPEN_MODE   RES
----------   ------------------  ----------  ---
2            PDB$SEED            READ ONLY   NO
3            [PDB12C]            MOUNTED

1. Data file information when connected from CDB:

SQL> select file_name from dba_data_files;

FILE_NAME
---------------------------------------------
[DIRECTORY]/o1_mf_users_b5p3vckg_.dbf
[DIRECTORY]/o1_mf_undotbs1_b5p3vdpw_.dbf
[DIRECTORY]/o1_mf_sysaux_b5p3msc7_.dbf
[DIRECTORY]/o1_mf_system_b5p3r2p0_.dbf
SQL> select file_name, con_id from cdb_data_files;

FILE_NAME                                    CON_ID
------------------------------------------  ----------
[DIRECTORY]/o1_mf_system_b5p3wqcd_.dbf         2
[DIRECTORY]/o1_mf_sysaux_b5p3wqbm_.dbf         2
[DIRECTORY]/o1_mf_users_b5p3vckg_.dbf          1
[DIRECTORY]/o1_mf_undotbs1_b5p3vdpw_.dbf       1
[DIRECTORY]/o1_mf_sysaux_b5p3msc7_.dbf         1
[DIRECTORY]/o1_mf_system_b5p3r2p0_.dbf         1

6 rows selected.

2. Data file informatiion when connected from PDB:

SQL> conn sys/******@PDB12C as sysdba
Connected.
SQL> select file_name from dba_data_files;
select file_name from dba_data_files
                      *
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed
tables or views only
SQL> select file_name, con_id from cdb_data_files;
select file_name, con_id from cdb_data_files
                              *
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed
tables or views only

Case 2 – PDB in restricted Mode

SQL> alter pluggable database [pdb12c] open restricted;
Pluggable database altered.
SQL> select con_id, name, open_mode, restricted from v$pdbs;

CON_ID        NAME                OPEN_MODE   RES
----------    ------------------  ----------  ---
2             PDB$SEED            READ ONLY   NO
3             [PDB12C]            READ WRITE  YES
SQL> select pdb_id, pdb_name, con_uid,status from dba_pdbs;

PDB_ID      PDB_NAME                   CON_UID     STATUS
----------  -------------------------  ----------  -------------
3           PDB12C                     515133920    NORMAL
2           PDB$SEED                   4105546291   NORMAL

1. Data file information when connected from CDB:

SQL> select file_name from dba_data_files;

FILE_NAME
------------------------------------------
[DIRECTORY]/o1_mf_users_b5p3vckg_.dbf
[DIRECTORY]/o1_mf_undotbs1_b5p3vdpw_.dbf
[DIRECTORY]/o1_mf_sysaux_b5p3msc7_.dbf
[DIRECTORY]/o1_mf_system_b5p3r2p0_.dbf
SQL> select file_name, con_id from cdb_data_files;

FILE_NAME                                  CON_ID
-----------------------------------------  ------
[DIRECTORY]/o1_mf_system_b5p3wqcd_.dbf      2
[DIRECTORY]/o1_mf_sysaux_b5p3wqbm_.dbf      2
[DIRECTORY]/o1_mf_users_b5p3vckg_.dbf       1
[DIRECTORY]/o1_mf_undotbs1_b5p3vdpw_.dbf    1
[DIRECTORY]/o1_mf_sysaux_b5p3msc7_.dbf      1
[DIRECTORY]/o1_mf_system_b5p3r2p0_.dbf      1

6 rows selected.

2. Data file informatiion when connected from PDB:

SQL> conn sys/******@pdb12c as sysdba
Connected.
SQL> select file_name from dba_data_files;

FILE_NAME
------------------------------------------
[DIRECTORY]/o1_mf_system_b5p46b02_.dbf
[DIRECTORY]/o1_mf_sysaux_b5p46b00_.dbf
[DIRECTORY]/o1_mf_users_b5p48mlg_.dbf
SQL> select file_name, con_id from cdb_data_files;

FILE_NAME                                  CON_ID
---------------------------------------   ----------
[DIRECTORY]/o1_mf_system_b5p46b02_.dbf      3
[DIRECTORY]/o1_mf_sysaux_b5p46b00_.dbf      3
[DIRECTORY]/o1_mf_users_b5p48mlg_.dbf       3

Case 3 – PDB in read write mode

SQL> alter pluggable database [pdb12c]open read write;

Pluggable database altered.
SQL> select con_id, name, open_mode, restricted from v$pdbs;

CON_ID      NAME         OPEN_MODE          RES
----------  -----------  ------------------ ------
2           PDB$SEED     READ ONLY           NO
3           [PDB12C]     READ WRITE          NO
SQL> select pdb_id, pdb_name, con_uid,status from dba_pdbs;

PDB_ID      PDB_NAME     CON_UID    STATUS
---------   ----------   ---------  -------
3           [PDB12C]     515133920  NORMAL
2           PDB$SEED     4105546291 NORMAL

1. Data file information when connected from CDB:

SQL> select file_name from dba_data_files;

FILE_NAME
------------------------------------------
[DIRECTORY]/o1_mf_users_b5p3vckg_.dbf
[DIRECTORY]/o1_mf_undotbs1_b5p3vdpw_.dbf
[DIRECTORY]/o1_mf_sysaux_b5p3msc7_.dbf
[DIRECTORY]/o1_mf_system_b5p3r2p0_.dbf
SQL> select file_name, con_id from cdb_data_files

FILE_NAME                                 CON_ID
---------------------------------------  ----------
[DIRECTORY]/o1_mf_system_b5p3wqcd_.dbf       2
[DIRECTORY]/o1_mf_sysaux_b5p3wqbm_.dbf       2
[DIRECTORY]/o1_mf_system_b5p46b02_.dbf       3
[DIRECTORY]/o1_mf_sysaux_b5p46b00_.dbf       3
[DIRECTORY]/o1_mf_users_b5p48mlg_.dbf        3
[DIRECTORY]/o1_mf_users_b5p3vckg_.dbf        1
[DIRECTORY]/o1_mf_undotbs1_b5p3vdpw_.dbf     1
[DIRECTORY]/o1_mf_sysaux_b5p3msc7_.dbf       1
[DIRECTORY]/o1_mf_system_b5p3r2p0_.dbf       1

9 rows selected.

2. Data file informatiion when connected from PDB:

SQL> conn sys/******@pdb12c as sysdba
Connected.
SQL> select file_name from dba_data_files;

FILE_NAME
---------------------------------------
[DIRECTORY]/o1_mf_system_b5p46b02_.dbf
[DIRECTORY]/o1_mf_sysaux_b5p46b00_.dbf
[DIRECTORY]/o1_mf_users_b5p48mlg_.dbf
SQL> select file_name, con_id from cdb_data_files;

FILE_NAME                                    CON_ID
------------------------------------------  ---------
[DIRECTORY]/o1_mf_system_b5p46b02_.dbf         3
[DIRECTORY]/o1_mf_sysaux_b5p46b00_.dbf         3
[DIRECTORY]/o1_mf_users_b5p48mlg_.dbf          3