Oracle Database 12c New Features: database files

Showing posts with label database files. Show all posts

All Files of the CDB

To view logfile that we can query v$logfile, we can see the con_id. In this example I am using ASM +DATA volume.

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.

Table 43-1 CON_ID Column in Container Data Objects

Value in CON_ID Column	Description
0	The data pertains to the entire CDB
1	The data pertains to the root
2	The data pertains to the seed
3 - 254	The data pertains to a PDB Each PDB has its own container ID.