ITPub博客

首页 > 数据库 > Oracle > 表空间常用sql汇总

表空间常用sql汇总

原创 Oracle 作者:kisslfcr 时间:2015-07-22 12:51:53 0 删除 编辑
表空间碎片相关:
select tablespace_name,
       round(sqrt(max(blocks) / sum(blocks)) *
             (100 / sqrt(sqrt(count(blocks)))),
             2) FSFI,
       (case
         when sqrt(max(blocks) / sum(blocks)) *
              (100 / sqrt(sqrt(count(blocks)))) > = 30 then
          '正常'
         when sqrt(max(blocks) / sum(blocks)) *
              (100 / sqrt(sqrt(count(blocks)))) < 30 then
          '表空间破碎化程度高,请整理'
       end) Prompt
  from dba_free_space
group by tablespace_name
order by 2;
--排除SYSAUX和UNDOTBS1

整理碎片: alter tablespace coalesce;

查看单个数据文件使用情况:
select distinct d.name,
       t.name,
       round(s.bytes / (1024 * 1024), 0) total_space,
       round(s.bytes / (1024 * 1024), 0)-round(f.bytes / (1024 * 1024), 0) used
  from v$tablespace t, v$datafile d, dba_data_files s, dba_free_space f
 where t.ts# = d.ts#
   and d.name = s.file_name
   and s.file_id = f.file_id
   and d.file# = f.file_id
   and t.name = f.tablespace_name

表空间使用率:
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
       D.TOT_GROOTTE_MB "表空间大小(M)",
       D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
       TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2),'990.99') || '%' "使用比(%)",
       F.TOTAL_BYTES "空闲空间(M)",
       F.MAX_BYTES "最大块(M)"
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
               ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
          FROM SYS.DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F,
       (SELECT DD.TABLESPACE_NAME,
               ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
          FROM SYS.DBA_DATA_FILES DD
         GROUP BY DD.TABLESPACE_NAME) D
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 desc;

查看某个表空间实际剩余空间sql:
select ((
nvl((select sum(t.maxblocks*8/1024) from dba_data_files t where t.autoextensible='YES'and t.tablespace_name = 'SYSTEM'),0)
+
nvl((select sum(t.blocks*8/1024) from dba_data_files t where t.autoextensible='NO'and t.tablespace_name = 'SYSTEM'),0))
-

SELECT D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)"
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
               ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
          FROM SYS.DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F,
       (SELECT DD.TABLESPACE_NAME,
               ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
          FROM SYS.DBA_DATA_FILES DD
         GROUP BY DD.TABLESPACE_NAME) D
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME AND D.TABLESPACE_NAME = 'SYSTEM'
)) "实际剩余空间(M)"
from dual;

数据文件使用率:
select tot.file_name,100*(tot.total-free.total)/tot.total||'%' from
(select file_name,file_id,sum(bytes) as total from dba_data_files tot group by file_id,file_name ) tot,
(select file_id,sum(bytes) as total from dba_free_space free group by file_id) free  
where tot.file_id=free.file_id

经测试,查看数据文件使用率,性能较好,较准确的:
 SELECT B.TABLESPACE_NAME,
         B.FILE_NAME,
         ROUND (B.BYTES / 1024 / 1024 / 1024, 2) "SIZE GB",
         ROUND (B.MAXBYTES / 1024 / 1024 / 1024, 2) "MAX SIZE GB",
         ROUND (SUM (A.BYTES) / 1024 / 1024 / 1024, 2) "FREE SPACE GB",
         CASE
            WHEN MAXBYTES = 0
            THEN
               ROUND (SUM (A.BYTES) / 1024 / 1024 / 1024, 2)
            ELSE
               ROUND (
                    ( (B.MAXBYTES - B.BYTES) + SUM (A.BYTES)) / 1024 / 1024 / 1024, 2)
         END
            "TOTAL FREE SPACE GB"
    FROM DBA_FREE_SPACE A, DBA_DATA_FILES B
   WHERE A.FILE_ID = B.FILE_ID AND B.TABLESPACE_NAME IN ('CBAM')
GROUP BY B.TABLESPACE_NAME,
         B.FILE_NAME,
         B.BYTES,
         B.MAXBYTES
ORDER BY 1, 2;

查看TEMP表空间使用情况:
SELECT a.tablespace_name,
       a.BYTES / 1024 / 1024 "total(M)",
       (a.bytes - nvl(b.bytes, 0)) / 1024 / 1024 "free(M)"
  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(+)

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29135257/viewspace-1744350/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2015-01-12

  • 博文量
    245
  • 访问量
    348735