Oracle space and usage

1. Calculation of the table's occupied space

Select S. SEGMENT_NAME, DECODE (SUM (BYTES), NULL, 0, SUM (BYTES)/1024/1024) Mbytes

From DBA_SEGMENTS S

Where S. SEGMENT_NAME = 'mtl _ MATERIAL_TRANSACTIONS '-- table name

And S. SEGMENT_TYPE = 'table'

Group By S. SEGMENT_NAME

2. Calculation of index space used

Select S. SEGMENT_NAME, DECODE (SUM (BYTES), NULL, 0, SUM (BYTES)/1024/1024) Mbytes

From DBA_SEGMENTS S

Where S. SEGMENT_NAME = 'mtl _ MATERIAL_TRANSACTIONS_N1 '-- Index

And S. SEGMENT_TYPE = 'index'

Group By S. SEGMENT_NAME

3. Calculation of the space occupied by partition tables

Select S. SEGMENT_NAME, DECODE (SUM (BYTES), NULL, 0, SUM (BYTES)/1024/1024) Mbytes

From DBA_SEGMENTS S

Where S. SEGMENT_TYPE = 'table partition'

Group By S. SEGMENT_NAME

4. Table space usage

Select a. TABLESPACE_NAME tablespace name,

Round (A. BYTES, 2) "total space (G )",

Round (B. BYTES, 2) "unused space (G )",

ROUND (A. BYTES-B. BYTES)/A. BYTES) * 100, 2) "usage %"

FROM (SELECT TABLESPACE_NAME, SUM (BYTES)/(1024*1024*1024) BYTES

FROM DBA_DATA_FILES

Group by TABLESPACE_NAME),

(SELECT TABLESPACE_NAME, SUM (BYTES)/(1024*1024*1024) BYTES

FROM DBA_FREE_SPACE

Group by TABLESPACE_NAME) B

Where a. TABLESPACE_NAME = B. TABLESPACE_NAME

Order by (A. BYTES-B. BYTES)/A. BYTES) DESC;