• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to get datafile information when PDB is Mounted/Read-Write/Restricted Mode

by admin

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

Filed Under: oracle, oracle 12c, Oracle 18c, Oracle 19c

Some more articles you might also be interested in …

  1. Where to find ASMLib / oracleasm RPMs for CentOS/RHEL, SUSE, OEL
  2. Oracle Database : Startup basics (How to start Oracle Database)
  3. How to Find the SQL_ID for a SQL statement or PL/SQL block
  4. Beginners Guide to Monitoring Oracle Database Restore/Recovery Progress
  5. How to rename database and then move datafile online in Oracle Database 12.2
  6. How to create restore points for PDB and perform flashback at PDB level
  7. Understanding Real-Time SQL Monitoring in Oracle 11g
  8. How to Enable or Disable Veritas ODM for Oracle database 11g
  9. Beginners Guide to Automatic Storage Management (ASM)
  10. Oracle 12c New Feature: LREG Background Process

You May Also Like

Primary Sidebar

Recent Posts

  • vgextend Command Examples in Linux
  • setpci command – configure PCI device
  • db_load command – generate db database
  • bsdtar command – Read and write tape archive files

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright