Which tables are in a given Oracle tablespace?

  • 3587128
  • 18-Jun-2007
  • 26-Apr-2012


Sentinel 5.1.3
Sentinel 6.0.xx


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.