By default when you create a pluggable database / pdb a “USERS” tablespace is created if you don’t specify a default tablespace in the “create pluggable database” command. So if you have a lot of pluggable databases, you will have many tablespaces names USERS.
Here’s how to display them all in a SQL statement connected to CDB$ROOT with SELECT_CATALOG_ROLE.
select p.name, d.file_name, d.tablespace_name, d.con_id
from cdb_data_files d,
v$pdbs p
where d.con_id=p.con_id
and tablespace_name like ‘USERS’
or if you want to see them all – order by tablespace_name
select p.name, d.file_name, d.tablespace_name, d.con_id
from cdb_data_files d,
v$pdbs p
where d.con_id=p.con_id
order by tablespace_name asc;