SQL> col member format a50
SQL> select GROUP#, CON_ID, MEMBER from v$logfile;
GROUP# CON_ID MEMBER
---------- ---------- --------------------------------------------------
1 0 +DATA/ORA12C/ONLINELOG/group_1.260.831911745
1 0 +DATA/ORA12C/ONLINELOG/group_1.261.831911755
2 0 +DATA/ORA12C/ONLINELOG/group_2.262.831911763
2 0 +DATA/ORA12C/ONLINELOG/group_2.263.831911771
3 0 +DATA/ORA12C/ONLINELOG/group_3.264.831911779
3 0 +DATA/ORA12C/ONLINELOG/group_3.265.831911787
6 rows selected.
To view controlfile
SQL> col name form a50
SQL> select name, con_id from v$controlfile;
NAME CON_ID
-------------------------------------------------- ----------
+DATA/ORA12C/CONTROLFILE/current.258.831911735 0
+DATA/ORA12C/CONTROLFILE/current.259.831911741 0
View all data files of the CDB, including those of the root and all PDBs. We can query cdb_data_files.
SQL> col file_name format A60
SQL> col tablespace_name format A8
SQL> col file_id format 9999
SQL> col con_id format 999
SQL> SELECT file_name, tablespace_name, file_id, con_id
2 FROM cdb_data_files ORDER BY con_id;
FILE_NAME TABLESPA FILE_ID CON_ID
---------------------------------------------------- -------- ------- ------
+DATA/ORA12C/DATAFILE/system.266.831911801 SYSTEM 1 1
+DATA/ORA12C/DATAFILE/users.273.831912215 USERS 6 1
+DATA/ORA12C/DATAFILE/undotbs1.270.831912051 UNDOTBS1 5 1
+DATA/ORA12C/DATAFILE/sysaux.268.831911935 SYSAUX 3 1
+DATA/ORA12C/PDBSEED/DATAFILE/sysaux.269.831912021 SYSAUX 4 2
+DATA/ORA12C/PDBSEED/DATAFILE/system.267.831911893 SYSTEM 2 2
6 rows selected.
Note that dba_data_files is list only root datafile.
SQL> col file_name form a50
SQL> select file_name, tablespace_name, file_id
2 from dba_data_files;
FILE_NAME TABLESPA FILE_ID
-------------------------------------------------- -------- -------
+DATA/ORA12C/DATAFILE/system.266.831911801 SYSTEM 1
+DATA/ORA12C/DATAFILE/sysaux.268.831911935 SYSAUX 3
+DATA/ORA12C/DATAFILE/undotbs1.270.831912051 UNDOTBS1 5
+DATA/ORA12C/DATAFILE/users.273.831912215 USERS 6
List the temp files of the CDB.
SQL> col file_name form a70
SQL> col tablespace_name form a10
SQL> col file_id form 999
SQL> select file_name, tablespace_name, file_id
2 from cdb_temp_files;
FILE_NAME TABLESPACE FILE_ID
---------------------------------------------------- ---------- -------
+DATA/ORA12C/PDBSEED/TEMPFILE/temp.272.831912083 TEMP 2
+DATA/ORA12C/TEMPFILE/temp.271.831912083 TEMP 1
Extracted from Oracle 12c Document, CON_ID = 0 mean CDB.
About Viewing Information When the Current Container Is the Root
When the current container is the root, a common user can view data dictionary information for the root and for PDBs by querying container data objects. A container data object is a table or view that can contain data pertaining to the following:
- One or more containers
- The CDB as a whole
- One or more containers and the CDB as a whole
Container data objects include
V$, GV$, CDB_, and some Automatic Workload Repository DBA_HIST* views. A common user's CONTAINER_DATA attribute determines which PDBs are visible in container data objects.In a CDB, for every
DBA_ view, there is a corresponding CDB_ view. All CDB_ views are container data objects, but most DBA_ views are not.Each container data object contains a
CON_ID column that identifies the container for each row returned. Table 43-1 describes the meanings of the values in the CON_ID column.