Which tables are in a given Oracle tablespace?

Knowing which data are filling a given tablespace is difficult when the tools all show usage by tablespace and not by table. Finding the tables in a tablespace makes it possible to see and report on the actual data taking up space to determine which actions should be taken.

To find all tables in the SENT_AUDITD tablespace the following command can be used when logged in as a database administrator.

SELECT table_name FROM all_all_tables WHERE tablespace_name='SENT_AUDITD';


Doing the same for other tablespaces simply requires changing the tablespace_name parameter at the end of the query.


For tablespaces that are partitioned the following query will be needed:

SELECT table_name, tablespace_name FROM all_tab_partitions WHERE tablespace_name='ESENTD';


Partitioned tables show up with a blank tablespace_name column in the first query and this second one is correct for these circumstances.