oracle 查看空闲表空间,查看Oracle表空间剩余

转载地址：http://blog.sina.com.cn/s/blog_6b8f75aa0101gr11.html

查看Oracle表空间剩余

//查看表空间剩余空间(M)

select tablespace_name,sum(bytes)/1024/1024 free_space

from dba_free_space

group by tablespace_name;

//详细查看表空间使用状况，包括总大小，使用空间，使用率，剩余空间

select t.*

from (SELECT D.TABLESPACE_NAME,

SPACE "SUM_SPACE(M)",

BLOCKS SUM_BLOCKS,

SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",

ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2)

"USED_RATE(%)",

FREE_SPACE "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 have tempfile

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(%)",

SPACE - USED_SPACE "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,

ROUND(SUM(BLOCKS * 8192) / (1024 * 1024), 2) USED_SPACE

FROM V$SORT_USAGE

GROUP BY TABLESPACE) F

WHERE D.TABLESPACE_NAME = F.TABLESPACE(+)) t

order by "USED_RATE(%)" desc;

SELECT tablespace_name, sum ( blocks ) as free_blk , trunc ( sum (

bytes ) / (1024*1024) ) as free_m, max ( bytes ) / (1024) as

big_chunk_k, count (*) as num_chunks

FROM dba_free_space

GROUP BY tablespace_name;