表空间容量查询，包括临时表空间（网上收集）

最新推荐文章于 2023-02-28 14:11:23 发布

tonyzhou_cn

最新推荐文章于 2023-02-28 14:11:23 发布

阅读量618

点赞数

本文链接：https://blog.csdn.net/tonyzhou_cn/article/details/8912567

版权

第1句：

select df.tablespace_name "tablespace_name",totalspace "totalspace(Mb)",freespace "freespace(Mb)",
       round((1 - freespace / totalspace) * 100, 2) "usedspace%"
      from (select tablespace_name, round(sum(bytes)/1024/1024) totalspace from dba_data_files group by tablespace_name) df,
       (select tablespace_name, round(sum(bytes)/1024/1024) freespace from dba_free_space group by tablespace_name) fs
      where df.tablespace_name = fs.tablespace_name order by 4 asc

第2句：可以查询所有表空间（包括临时表空间）
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(%)",
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(+)

查询临时表空间容量及使用比率
第1句：

SELECT D.TABLESPACE_NAME "表空间名称",SPACE "总容量(M)",
USED_SPACE "已使用容量(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "已使用百分比(%)",
NVL(FREE_SPACE,0) "剩余容量(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(+);

第2句：

SELECT a.tablespace_name,
a.BYTES/1024/1024 "total",
nvl(b.bytes/1024/1024, 0) "used",
nvl(b.bytes, 0)/a.BYTES "idel percent"
FROM (SELECT tablespace_name, SUM (bytes) bytes
FROM dba_temp_files GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM (bytes_cached) bytes
FROM v$temp_extent_pool GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+);

第3句：11G 才能用
select t.tablespace_name,
    t.tablespace_size/(1024*1024) total,
    t.free_space/(1024*1024) free,
    t.free_space/t.tablespace_size
from dba_temp_free_space t;