oracle查看表空间使用率(包含临时表空间)

最新推荐文章于 2024-02-21 11:09:14 发布

努力的小不点丶

最新推荐文章于 2024-02-21 11:09:14 发布

阅读量796

点赞数

分类专栏：数据库文章标签： oracle 数据库

本文链接：https://blog.csdn.net/m0_58042817/article/details/132099022

版权

数据库专栏收录该内容

13 篇文章 0 订阅

订阅专栏

SELECT
 d.tablespace_name "Name",
 d.status "Status",
 d.contents "Type",
 to_char(nvl(a.bytes / 1024 / 1024, 0), '99G999G999D99') "Size 
(MB)",
 to_char(NVL(f.bytes, 0)/1024/1024,'99G999G999D99') "Free 
(MB)",
 to_char(nvl(a.bytes - nvl(f.bytes, 0), 0) / 1024 / 1024,
'99G999G999D99') "Used (MB)",
 to_char(nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100, 0),
'999D99') "Used%"
FROM
 sys.dba_tablespaces d,
 (
 SELECT
 tablespace_name,
 SUM(bytes) bytes
 FROM
 dba_data_files
 GROUP BY
 tablespace_name
 ) a,
 (
 SELECT
 tablespace_name,
 SUM(bytes) bytes
 FROM
 dba_free_space
 GROUP BY
 tablespace_name
 ) f
WHERE
 d.tablespace_name = a.tablespace_name (+)
 AND d.tablespace_name = f.tablespace_name (+)
AND NOT (
 d.extent_management like 'LOCAL'
 AND
 d.contents like 'TEMPORARY'
 )
UNION ALL
SELECT
 d.tablespace_name "Name",
 d.status "Status",
 d.contents "Type",
 to_char(nvl(a.bytes / 1024 / 1024, 0), '99G999G999D99') "Size 
(MB)",
 to_char(nvl(a.bytes - nvl(t.bytes, 0), 0) / 1024 / 1024,
'99G999G999D99') "Free (MB)",
 to_char(nvl(t.bytes, 0) / 1024 / 1024, '99G999G999D99') "Used 
(MB)",
 to_char(nvl(t.bytes, 0) / a.bytes * 100, '999D99') "Used%"
FROM
 sys.dba_tablespaces d,
 (
 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
 ) t
WHERE
 d.tablespace_name = a.tablespace_name (+)
 AND d.tablespace_name = t.tablespace_name (+)
 AND d.extent_management LIKE 'LOCAL'
 AND d.contents LIKE 'TEMPORARY'
ORDER BY 7 desc;