Skip to content

How to List Tables or Indexes in a Tablespace

We have talked about the differences between schema objects and segments in another post. From tablespace's point of view, program units and informative schema objects go to the SYSTEM tablespace, concrete segments go to custome tablespaces.

In this post, we'd like to list all segments in a custom tablespace, e.g. EXAMPLE tablespace.

Order by Name

Here we query DBA_SEGMENTS to retrieve the information.

SQL> column owner format a10;
SQL> select owner, segment_type, segment_name from dba_segments where tablespace_name = 'EXAMPLE' order by 1,2,3;

---------- ------------------ ------------------------------
HR         INDEX              COUNTRY_C_ID_PK
HR         INDEX              DEPT_ID_PK
HR         INDEX              DEPT_LOCATION_IX
275 rows selected.

Order by Size

To list top objects in size, we can do this:

SQL> column "SIZE(MB)" format 999.99;
SQL> select owner, segment_type, segment_name, bytes/1024/1024 "SIZE(MB)" from dba_segments where tablespace_name = 'EXAMPLE' order by 4 desc;

OWNER      SEGMENT_TYPE       SEGMENT_NAME                     SIZE(MB)
---------- ------------------ ------------------------------ ----------
SH         TABLE              CUSTOMERS                              13.00
SH         TABLE PARTITION    SALES                                   8.00
SH         TABLE PARTITION    SALES                                   8.00

Leave a Reply

Your email address will not be published.