There is only one (1) UNDO tablespace in a CDB. There is a TEMP tablespace for each PDB.
I am just going to see what tablespaces can be seen at the PDB and CDB levels from both fixed and dynamic views.
Run the following SQL command to see what tablespaces are visible through the fixed views (CDB_*) for the CDB:
set linesize 180 set pagesize 100 column pdb_name format a15 select pdb_name, tablespace_name from cdb_tablespaces inner join cdb_pdbs using (con_id) order by pdb_name, tablespace_name;
The expected output is:
PDB_NAME TABLESPACE_NAME --------------- ------------------------------ JAM SYSAUX JAM SYSTEM JAM TEMP JAM USERS JAM0 SYSAUX JAM0 SYSTEM JAM0 TEMP JAM0 USERS JAM1 SYSAUX JAM1 SYSTEM JAM1 TEMP JAM1 USERS PLUM SYSAUX PLUM SYSTEM PLUM TEMP PLUM USERS VEGEMITE SYSAUX VEGEMITE SYSTEM VEGEMITE TEMP VEGEMITE USERS VEGEMITER SYSAUX VEGEMITER SYSTEM VEGEMITER TEMP VEGEMITER USERS 24 rows selected.
Note: There are no entries for both the root (CDB$ROOT) and the seed (PDB$SEED).
Run the following SQL command to see what tablespaces are visible through the dynamic views (V$*) for the CDB:
select c.name as pdb_name, t.name as tablespace_name from V$TABLESPACE t inner join V$CONTAINERS c using (con_id) order by pdb_name, tablespace_name;
The expected output is:
PDB_NAME TABLESPACE_NAME --------------- ------------------------------ CDB$ROOT SYSAUX CDB$ROOT SYSTEM CDB$ROOT TEMP CDB$ROOT UNDOTBS1 CDB$ROOT USERS JAM SYSAUX JAM SYSTEM JAM TEMP JAM USERS JAM0 SYSAUX JAM0 SYSTEM JAM0 TEMP JAM0 USERS JAM1 SYSAUX JAM1 SYSTEM JAM1 TEMP JAM1 USERS PDB$SEED SYSAUX PDB$SEED SYSTEM PDB$SEED TEMP PDB$SEED USERS PLUM SYSAUX PLUM SYSTEM PLUM TEMP PLUM USERS VEGEMITE SYSAUX VEGEMITE SYSTEM VEGEMITE TEMP VEGEMITE USERS VEGEMITER SYSAUX VEGEMITER SYSTEM VEGEMITER TEMP VEGEMITER USERS 33 rows selected.
Note: There are now entries from both the root (CDB$ROOT) and the seed (PDB$SEED). These account for the extra nine (9) rows returned.
Note: The UNDO tablepace only appears in the root (CDB$ROOT).
Connect to the PLUM PDB with the following SQL command:
alter session set container=plum;
The expected output is:
Session altered.
Run the following SQL command to see what tablespaces are visible through the fixed views (CDB_*) for this PDB:
select pdb_name, tablespace_name from cdb_tablespaces inner join cdb_pdbs using (con_id) order by pdb_name, tablespace_name;
The expected output is:
PDB_NAME TABLESPACE_NAME --------------- ------------------------------ PLUM SYSAUX PLUM SYSTEM PLUM TEMP PLUM USERS
As expected, only the tablespaces for that PDB are returned.
Run the following SQL command to see what tablespaces are visible through the dynamic views (V$*) for this PDB:
select c.name as pdb_name, t.name as tablespace_name from V$TABLESPACE t inner join V$CONTAINERS c using (con_id) order by pdb_name, tablespace_name;
The expected output is:
PDB_NAME TABLESPACE_NAME --------------- ------------------------------ PLUM SYSAUX PLUM SYSTEM PLUM TEMP PLUM USERS
There is no difference with the results returned by the fixed views.