SQL> col name form a12
SQL> SELECT file#, tbs.name, tbs.ts#, tbs.con_id
2 FROM v$datafile dbf, v$tablespace tbs
3 WHERE dbf.ts#=tbs.ts#
4 AND dbf.con_id=tbs.con_id;
FILE# NAME TS# CON_ID
---------- ------------ ---------- ------
1 SYSTEM 0 1
2 SYSTEM 0 2
3 SYSAUX 1 1
4 SYSAUX 1 2
5 UNDOTBS1 2 1
6 USERS 4 1
7 SYSTEM 0 3
8 SYSAUX 1 3
9 USERS 3 3
9 rows selected.
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.