查询Oracle表空间使用情况

最新推荐文章于 2024-09-14 14:09:59 发布

Triagen

最新推荐文章于 2024-09-14 14:09:59 发布

阅读量362

点赞数

分类专栏： Oracle工作笔记文章标签： Oracle

本文链接：https://blog.csdn.net/Triagen/article/details/86647119

版权

Oracle工作笔记专栏收录该内容

1 篇文章 0 订阅

订阅专栏

参考文献

dba_free_space : reference_link_for_dba_free_space
dba_data_files : reference_link_for_dba_data_files

查询剩余表空间

SET lines 2000
SET pagesize 2000

SELECT tablespace_name,
       Count(*)                           AS extends,
       Round(Sum(bytes) / 1024 / 1024, 2) AS mb,
       Sum(blocks)                        AS blocks
FROM   dba_free_space
GROUP  BY tablespace_name
ORDER  BY tablespace_name;

查询表空间总容量

SET lines 2000
SET pagesize 2000

SELECT tablespace_name,
       Round(Sum(user_bytes) / 1024 / 1024, 2) AS mb
FROM   dba_data_files
GROUP  BY tablespace_name
ORDER  BY tablespace_name;

查询表空间使用率

SET lines 2000
SET pagesize 2000

SELECT total.tablespace_name,
       Round(total.mb, 2)           AS total_mb,
       Round(total.mb - free.mb, 2) AS used_mb,
       Round(( 1 - free.mb / total.mb ) * 100, 2)
       || '%'                       AS used_pct
FROM   (SELECT tablespace_name,
               Sum(bytes) / 1024 / 1024 AS mb
        FROM   dba_free_space
        GROUP  BY tablespace_name) free,
       (SELECT tablespace_name,
               Sum(user_bytes) / 1024 / 1024 AS mb
        FROM   dba_data_files
        GROUP  BY tablespace_name) total
WHERE  free.tablespace_name = total.tablespace_name
ORDER  BY tablespace_name;