已索引

根据数据文件最大大小统计表空间使用情况（不含临时表空间）

set lines 800;
set pages 800;
col tablespace_name for a30;
col used_pct for a10;

select total.TABLESPACE_NAME TABLESPACE_NAME,
total.MAX_SIZE_MB MAX_SIZE_MB, total.SIZE_MB-free.USED_FREE_MB USED_SIZE_MB,
round(((total.SIZE_MB-free.USED_FREE_MB)/total.MAX_SIZE_MB)*100,2)|| '%'  USED_PCT, 
total.MAX_SIZE_MB-(total.SIZE_MB-free.USED_FREE_MB) Free_MB
from
    (select TABLESPACE_NAME,
sum(BYTES)/1024/1024 SIZE_MB,
sum(decode(MAXBYTES, 0 , BYTES,MAXBYTES ))/1024/1024 MAX_SIZE_MB
 from dba_data_files 
group by TABLESPACE_NAME) total, 
    (select TABLESPACE_NAME,
sum(BYTES)/1024/1024 USED_FREE_MB
from dba_free_space
group by TABLESPACE_NAME) free
    where total.TABLESPACE_NAME=free.TABLESPACE_NAME 
and total.TABLESPACE_NAME not like 'UNDO%' ;

根据数据文件当前大小统计表空间使用情况（不包含临时表空间）

set lines 800;
set pages 800;
col tablespace_name for a30;
col used_pct for a10;

select total.TABLESPACE_NAME TABLESPACE_NAME,
total.SIZE_MB MAX_SIZE_MB, total.SIZE_MB-free.USED_FREE_MB USED_SIZE_MB,
round(((total.SIZE_MB-free.USED_FREE_MB)/total.SIZE_MB)*100,2)|| '%'  USED_PCT, 
free.USED_FREE_MB Free_MB
from
    (select TABLESPACE_NAME,sum(BYTES)/1024/1024 SIZE_MB
         from dba_data_files 
        group by TABLESPACE_NAME) total, 
    (select TABLESPACE_NAME,sum(BYTES)/1024/1024 USED_FREE_MB
        from dba_free_space
        group by TABLESPACE_NAME) free
    where total.TABLESPACE_NAME=free.TABLESPACE_NAME 
and total.TABLESPACE_NAME not like 'UNDO%' ;

根据数据文件最大大小统计临时表空间使用情况

set lines 800;
set pages 800;
col tablespace_name for a30;
col used_pct for a10;

select c.TABLESPACE_NAME,
c.MAXBYTES/1024/1024 TOTAL_SIZE_MB,
d.BYTES_USED/1024/1024 USED_SIZE_MB,
round((d.BYTES_USED/c.MAXBYTES)*100,2) || '%' USED_PCT,
(c.MAXBYTES-d.BYTES_USED)/1024/1024 FREE_MB
from
(select TABLESPACE_NAME,sum(decode(MAXBYTES,0,BYTES,MAXBYTES)) MAXBYTES,sum(BYTES) BYTES
from dba_temp_files group by TABLESPACE_NAME) c,
(select TABLESPACE_NAME,sum(BYTES_CACHED) BYTES_USED
from v$temp_extent_pool group by TABLESPACE_NAME) d
where c.TABLESPACE_NAME = d.TABLESPACE_NAME;

可以使用 UNION ALL 运算将永久表空间和临时表空间的统计整合起来。