v_tablespace 表空间查询视图

最新推荐文章于 2024-08-20 15:02:46 发布

甲骨灰

最新推荐文章于 2024-08-20 15:02:46 发布

阅读量1k

点赞数

分类专栏： ORACLE

本文链接：https://blog.youkuaiyun.com/XmallWood/article/details/12202581

版权

ORACLE 专栏收录该内容

45 篇文章

订阅专栏

select a.tablespace_name 表空间,
       a.total_bytes - nvl(b.free_bytes, 0) 已使用M,
       a.total_bytes 总计M,
       round((a.total_bytes - nvl(b.free_bytes, 0)) / a.total_bytes, 4) * 100 || '%' 已使用百分比,
       nvl(b.free_bytes, 0) || 'M' 剩余,
       round(nvl(b.free_bytes, 0) / a.total_bytes, 4) * 100 || '%' 剩余百分比,
       round((a.total_bytes - nvl(b.free_bytes, 0)) / a.total_bytes, 4) * 100 used
from (select df.tablespace_name, sum(df.bytes) / 1024 / 1024 Total_bytes
          from dba_data_files Df
          Where df.TABLESPACE_NAME NOT Like 'UNDOTBS%'
         group by df.tablespace_name) a,
       (select fs.tablespace_name, sum(fs.bytes) / 1024 / 1024 Free_bytes
        from dba_free_space fs
        Where fs.TABLESPACE_NAME NOT Like 'UNDOTBS%'
         group by fs.tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)
Union All
SELECT uA.Tablespace_Name 表空间,
       Used_Undo 已使用M,
       Total_Undo 总计M,
       Trunc(Used_Undo / Total_Undo * 100, 2)||'%' 已使用百分比,
       (Total_Undo - Used_Undo)||'M' 剩余,
       Trunc((Total_Undo - Used_Undo) / Total_Undo * 100, 2)||'%' 剩余百分比,
       Trunc(Used_Undo / Total_Undo * 100, 2) used
FROM   (SELECT Nvl(SUM(Bytes / 1024 / 1024), 0) Used_Undo,
               Tablespace_Name
        FROM   Dba_Undo_Extents
        WHERE Status IN ('ACTIVE', 'UNEXPIRED')
        GROUP BY Tablespace_Name) ua,
       (SELECT Tablespace_Name,
               SUM(Bytes / 1024 / 1024) Total_Undo
        FROM   Dba_Data_Files
        WHERE Tablespace_Name Like 'UNDOTBS%'
        GROUP BY Tablespace_Name) ub
WHERE uA.Tablespace_Name = uB.Tablespace_Name
Union All
Select   d.Tablespace_Name 表空间,
       Nvl(t.Bytes, 0) / 1024 / 1024 已使用M,
       Nvl(a.Bytes / 1024 / 1024, 0) 总计M,
       trunc(Nvl(t.Bytes / a.Bytes * 100, 0), 2)||'%' 已使用百分比,
       (a.bytes-Nvl(t.bytes,0))/1024/1024||'M' 剩余,
       trunc(Nvl((a.bytes-Nvl(t.bytes,0)) / a.Bytes * 100, 0),2)||'%' 剩余百分比,
       trunc(Nvl(t.Bytes / a.Bytes * 100, 0),2) "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 gV$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;