oracle用户表空间是使用率临界值多少合适

解决方案1:

    不宜超过80%,除非你表空间内的表数据都是完全连续存储的。

解决方案2:

    设成可自动拓展的,就不用管这些了

1、查询表空间使用情况
  1. SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
  2.   D.TOT_GROOTTE_MB "表空间大小(M)",
  3.   D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
  4.   TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
  5.   F.TOTAL_BYTES "空闲空间(M)",
  6.   F.MAX_BYTES "最大块(M)"
  7.   FROM (SELECT TABLESPACE_NAME,
  8.   ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
  9.   ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
  10.   FROM SYS.DBA_FREE_SPACE
  11.   GROUP BY TABLESPACE_NAME) F,
  12.   (SELECT DD.TABLESPACE_NAME,
  13.    ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
  14.   FROM SYS.DBA_DATA_FILES DD
  15.   GROUP BY DD.TABLESPACE_NAME) D
  16.   WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
  17.   ORDER BY 1;
  18.   --查询表空间的free space
  19.   select tablespace_name,
  20.   count(*) as extends,
  21.   round(sum(bytes) / 1024 / 1024, 2) as MB,
  22.   sum(blocks) as blocks
  23.   from dba_free_space
  24.   group by tablespace_name;
复制代码

2、查询表空间的总容量

  1. select tablespace_name, sum(bytes) / 1024 / 1024 as MB
  2.   from dba_data_files
  3.   group by tablespace_name;
复制代码

3、查询表空间使用率

  1. select total.tablespace_name,
  2.   round(total.MB, 2) as Total_MB,iidba
  3.   round(total.MB - free.MB, 2) as Used_MB,
  4.   round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct
  5.   from (select tablespace_name, sum(bytes) / 1024 / 1024 as MB
  6.   from dba_free_space
  7.   group by tablespace_name) free,
  8.   (select tablespace_name, sum(bytes) / 1024 / 1024 as MB
  9.   from dba_data_files
  10.   group by tablespace_name) total
  11.   where free.tablespace_name = total.tablespace_name;
复制代码


标签: 暂无标签
iidba

写了 205 篇文章,拥有财富 2829,被 218 人关注

转播转播 分享分享 分享淘帖
回复

使用道具

成为第一个吐槽的人

您需要登录后才可以回帖 登录 | 加入社区

本版积分规则

意见
反馈