用户表空间
select tablespace_name, total_mb, used_mb, free_mb, used_pct from (
select t. tablespace_name, t. total_mb, t. total_mb- f. total_free_mb used_mb, total_free_mb free_mb,
to_char( ( 1 - ( f. total_free_mb/ t. total_mb) ) * 100 , '990.99' ) || '%' as used_pct
from
( select tablespace_name, round ( sum ( bytes) / ( 1024 * 1024 ) ) total_mb
from dba_data_files group by tablespace_name) t,
( select tablespace_name, round ( sum ( bytes) / ( 1024 * 1024 ) ) total_free_mb
from dba_free_space group by tablespace_name) f
where t. tablespace_name = f. tablespace_name
) ;
临时表空间
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( + ) ;