set line 300 pages 2000
Select d.tablespace_name,
Space "Sum_space(M)",
Blocks sum_blocks,
Space - nvl(free_space, 0) "Used_space(M)",
round((1 - nvl(free_space, 0) / Space) * 100, 2) "Used_rate(%)",
free_space "Free_space(M)"
From (Select tablespace_name,
round(Sum(bytes) / (1024 * 1024), 2) Space,
Sum(blocks) Blocks
From dba_data_files
Group By tablespace_name) d,
(Select tablespace_name,
round(Sum(bytes) / (1024 * 1024), 2) free_space
From dba_free_space
Group By tablespace_name) f
Where d.tablespace_name = f.tablespace_name(+)
Union All
Select d.tablespace_name,
Space "Sum_space(M)",
blocks sum_blocks,
used_space "Used_space(M)",
round(nvl(used_space, 0) / Space * 100, 2) "Used_rate(%)",
nvl(free_space, 0) "Free_space(M)"
From (Select tablespace_name,
round(Sum(bytes) / (1024 * 1024), 2) Space,
Sum(blocks) blocks
From dba_temp_files
Group By tablespace_name) d,
(Select tablespace_name,
round(Sum(bytes_used) / (1024 * 1024), 2) used_space,
round(Sum(bytes_free) / (1024 * 1024), 2) free_space
From v$temp_space_header
Group By tablespace_name) f
Where d.tablespace_name = f.tablespace_name(+);
--------eg.
TABLESPACE_NAME Sum_space(M) SUM_BLOCKS Used_space(M) Used_rate(%) Free_space(M)
------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
BOSSDATA 784248 100383744 187199.44 23.87 597048.56
SYSTEM 2047 262016 299.5 14.63 1747.5
UNDOTBS1 40867 5230976 1192.06 2.92 39674.94
TEMP 16380 2096640 11786 71.95 4594
Select d.tablespace_name,
Space "Sum_space(M)",
Blocks sum_blocks,
Space - nvl(free_space, 0) "Used_space(M)",
round((1 - nvl(free_space, 0) / Space) * 100, 2) "Used_rate(%)",
free_space "Free_space(M)"
From (Select tablespace_name,
round(Sum(bytes) / (1024 * 1024), 2) Space,
Sum(blocks) Blocks
From dba_data_files
Group By tablespace_name) d,
(Select tablespace_name,
round(Sum(bytes) / (1024 * 1024), 2) free_space
From dba_free_space
Group By tablespace_name) f
Where d.tablespace_name = f.tablespace_name(+)
Union All
Select d.tablespace_name,
Space "Sum_space(M)",
blocks sum_blocks,
used_space "Used_space(M)",
round(nvl(used_space, 0) / Space * 100, 2) "Used_rate(%)",
nvl(free_space, 0) "Free_space(M)"
From (Select tablespace_name,
round(Sum(bytes) / (1024 * 1024), 2) Space,
Sum(blocks) blocks
From dba_temp_files
Group By tablespace_name) d,
(Select tablespace_name,
round(Sum(bytes_used) / (1024 * 1024), 2) used_space,
round(Sum(bytes_free) / (1024 * 1024), 2) free_space
From v$temp_space_header
Group By tablespace_name) f
Where d.tablespace_name = f.tablespace_name(+);
--------eg.
TABLESPACE_NAME Sum_space(M) SUM_BLOCKS Used_space(M) Used_rate(%) Free_space(M)
------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
BOSSDATA 784248 100383744 187199.44 23.87 597048.56
SYSTEM 2047 262016 299.5 14.63 1747.5
UNDOTBS1 40867 5230976 1192.06 2.92 39674.94
TEMP 16380 2096640 11786 71.95 4594