Copy Code code as follows:
/* Formatted on 2012/5/31 14:51:13 (QP5 v5.185.11230.41888) * *
SELECT D.tablespace_name,
Space | | ' m ' "Sum_space (m)",
BLOCKS "Sum_blocks",
SPACE-NVL (free_space, 0) | | ' m ' "Used_space (m)",
ROUND ((1-NVL (free_space, 0)/space) * 100, 2) | | '%'
"Used_rate (%)",
Free_space | | ' m ' "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--if there is a temporary table space
SELECT D.tablespace_name,
Space | | ' m ' "Sum_space (m)",
BLOCKS Sum_blocks,
Used_space | | ' m ' "Used_space (m)",
ROUND (NVL (used_space, 0)/space * 100, 2) | | '% ' "used_rate (%)",
NVL (free_space, 0) | | ' m ' "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 (+)
Order by 1;
The effect is as follows:
