oracle临时表空间查看

最新推荐文章于 2024-04-18 05:32:12 发布

韶博雅

最新推荐文章于 2024-04-18 05:32:12 发布

阅读量1k

点赞数 8

文章标签： oracle 数据库

本文链接：https://blog.csdn.net/weixin_39735909/article/details/136206829

版权

select *
  from (Select a.tablespace_name,
               (a.bytes - b.bytes) "表空间使用大小(BYTE)",
               a.bytes / (1024 * 1024 * 1024) "表空间大小(GB)",
               b.bytes / (1024 * 1024 * 1024) "表空间剩余大小(GB)",
               (a.bytes - b.bytes) / (1024 * 1024 * 1024) "表空间使用大小(GB)",
               to_char((1 - b.bytes / a.bytes) * 100, '99.99999') || '%' "使用率"
          from (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) b
         where a.tablespace_name = b.tablespace_name
        union all
        select c.tablespace_name,
               d.bytes_used "表空间使用大小(BYTE)",
               c.bytes / (1024 * 1024 * 1024) "表空间大小(GB)",
               (c.bytes - d.bytes_used) / (1024 * 1024 * 1024) "表空间剩余大小(GB)",
               d.bytes_used / (1024 * 1024 * 1024) "表空间使用大小(GB)",
               to_char(d.bytes_used * 100 / c.bytes, '99.99999') || '%' "使用率"
          from (select tablespace_name, sum(bytes) bytes
                  from dba_temp_files
                 group by tablespace_name) c,
               (select tablespace_name, sum(bytes_used) bytes_used
                  from v$temp_extent_pool
                 group by tablespace_name) d
         where c.tablespace_name = d.tablespace_name)
 order by tablespace_name;
 
 
 
 
 
 
 
 
 
 
 
 
 SELECT D.tablespace_name,
       SPACE "SUM_SPACE(M)",
       blocks "SUM_BLOCKS",
       used_space "USED_SPACE(M)",
       Round(Nvl(used_space, 0) / SPACE * 100, 2) "USED_RATE(%)",
       SPACE - used_space "FREE_SPACE(M)"
  FROM (SELECT tablespace_name,
               Round(SUM(bytes) / (1024 * 1024), 2) SPACE,
               SUM(blocks) BLOCKS
          FROM dba_temp_files
         GROUP BY tablespace_name) D,
       (SELECT tablespace,
               Round(SUM(blocks * 8192) / (1024 * 1024), 2) USED_SPACE
          FROM v$sort_usage
         GROUP BY tablespace) F
 WHERE D.tablespace_name = F.tablespace(+)
   AND D.tablespace_name in ('TEMP', 'TEMP1')