# 数据表空间、临时表空间剩余空间查询语句

1. 临时表空间的剩余空间查询语句.

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
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%

• 博文量
15
• 访问量
48477