查询数据表空间和临时表空间的剩余空间，在我们日常对数据库维护时经常使用，由于查询语句较复杂，在这里跟大家分享一下，以下查询语句适用与9I、10G：

SQL> set line 200
SQL> SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,USED_SPACE "USED_SPACE(M)",
  2  ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",NVL(FREE_SPACE,0) "FREE_SPACE(M)"
  3  FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
  4  FROM DBA_TEMP_FILES
  5  GROUP BY TABLESPACE_NAME) D,
  6  (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
  7  ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
  8  FROM V$TEMP_SPACE_HEADER
  9  GROUP BY TABLESPACE_NAME) F
 10  WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);

TABLESPACE_NAME                SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M)
------------------------------ ------------ ---------- ------------- ------------ -------------
TEMP                                    100      12800            16           16            84

2. 数据表空间的剩余空间查询语句

SQL> SET LINE 200

SQL> select free.tablespace_name talbespace_name,free.msize free_msize,tt.msize total_size,to_char(free.msize*100/tt.msize,'999.99')||'%' free_percent
  2  from
  3  (select tablespace_name,sum(bytes)/(1024*1024) Msize
  4  from dba_free_space
  5  group by tablespace_name) free,
  6  v$tablespace,
  7  (select ts#,sum(bytes)/(1024*1024) Msize
  8  from v$datafile
  9  group by ts#) tt
 10  where free.tablespace_name=v$tablespace.name
 11  and v$tablespace.ts#=tt.ts#
 12  order by 4 desc,2 desc;

TALBESPACE_NAME                FREE_MSIZE TOTAL_SIZE FREE_PER
------------------------------ ---------- ---------- --------
INDX                              99.9375        100   99.94%
STRTBS                            49.9375         50   99.88%
UNDOTBS1                          194.375        492   39.51%
SYSTEM                           117.3125        400   29.33%
USERS02                            1.1875         10   11.88%
SYSAUX                              15.25        200    7.63%