How to check segment and total of tablespace size on Oracle Databases 10g onward
Segments
The level of logical database storage greater than an extent is called a segment. A segment is a set of extents, each of which has been allocated for a specific data structure and all of which are stored in the same tablespace.
A segment and all its extents are stored in one tablespace. Within a tablespace, a segment can include extents from more than one file; that is, the segment can span datafiles. However, each extent can contain data from only one datafile.
I wrote 2 options below to check segments you can choose the best one that you think. ^^
1.
—show all as a Tablespace, used of Tablespace, Free size of Tablespace, the total size of the tablespace
| SELECT DF.TABLESPACE_NAME “TABLESPACE”,TS.CONTENTS “TS TYPE”,TS.SEGMENT_SPACE_MANAGEMENT “SEGMENT SP.”, DF.TOTALSPACE “TOTAL MB”, (DF.TOTALSPACE – TTSU.TOTALUSEDSPACE) “FREE MB”, TOTALUSEDSPACE “USED MB”, ROUND(100 * ( (DF.TOTALSPACE – TTSU.TOTALUSEDSPACE)/ DF.TOTALSPACE)) “PCT. FREE” FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / 1048576) TOTALSPACE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) DF, (SELECT ROUND(SUM(BYTES)/(1024*1024)) TOTALUSEDSPACE, TABLESPACE_NAME FROM DBA_SEGMENTS GROUP BY TABLESPACE_NAME) TTSU, DBA_TABLESPACES TS WHERE DF.TABLESPACE_NAME = TTSU.TABLESPACE_NAME AND DF.TABLESPACE_NAME = TS.TABLESPACE_NAME ORDER BY DF.TABLESPACE_NAME; |
2.
| —Summary tablespace —Summary size usedSELECT TABLESPACE_NAME,ROUND(SUM(BYTES/1024/1024),2) USED_MB FROM DBA_SEGMENTS GROUP BY TABLESPACE_NAME ORDER BY TABLESPACE_NAME; —Summary tablespace SELECT TABLESPACE_NAME,ROUND(SUM(BYTES/1024/1024)) TOTAL_MB |