sqloracle-databasetablespace

How to get all tablespaces that a table belongs to in Oracle?

I know how to get ONE tablespace that a table belongs to:

select table_name,tablespace_name from dba_tables where owner = 'OWNER'
order by table_name;

This query returns

TABLE_NAME | TABLESPACE_NAME
 ----------------------------
 TABLE1     | TABLESPACE_A
 TABLE2     | TABLESPACE_A
 TABLE3     | TABLESPACE_B

However, this only returns ONE of the tablespaces that each table belongs to. I know, for example, that all tables are also part of the USERS tablespace, but the above query does not show that, it only shows ONE tablespace per table.

So how do I see ALL the tablespaces that a table belongs to, including the USERS tablespace?

Solution

  • Ok, here it is:

    The entire concept of "all the tablespaces that are assigned to a table" is a nullity. A table can belong to one and only one tablespace.

    spo demo.log
select owner,
       segment_name,
       segment_type,
       tablespace_name
from dba_segments
order by owner,
         segment_name,
         segment_type,
         tablespace_name
;
spo off
edit demo.log