Table Resides in What Tablespace?
To better manage overall tablespace usage, we'd like to know which tablespace specific table or index is in before moving it. In other words, we'd like to know the tablespace that a table belongs to.
Let's see the query.
SQL> select distinct tablespace_name from dba_segments where owner = 'SH' and segment_name = 'SALES' and segment_type like '%TABLE%';
TABLESPACE_NAME
------------------------------
EXAMPLE
In the statement, we provided several information for DBA_SEGMENTS, one is the OWNER, the other is the SEGMENT_NAME. SEGMENT_TYPE can be optional, but it's safer to use it in WHERE clause.
Another way to know the tablespace of the table reside in is to get DDL of the table, then check TABLESPACE clause in it.
After knowing the original tablespace owns the table or index, you may now consider to move the table or move the index to another tablespace.