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