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 permanent 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;
OWNER SEGMENT_TYPE SEGMENT_NAME
---------- ------------------ ------------------------------
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
...
Reversely, to check the tablespace name of a specific table, we have some way to do it.