正德厚生,臻于至善

Oracle ts.sql

set linesize 200
set pagesize 200
col tablespace_name format a20
col extent_management format a20
col "spc_used" format a15
col "free/used/alloted/limited" format a30
col "used/ALLOC%" format 9999999
col "used/MAX%" format 9999999

With tbsstat As (
select  a.tablespace_name,
        (a.bytes_alloc - nvl(b.bytes_free, 0)) sizeold,
      (CASE
            WHEN round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024/1024, 0) < 1 THEN
              round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 0) ||'m'
            ELSE
              round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024/1024, 0) ||'g'
       END) "spc_used",
       t.contents,t.status,t.extent_management ,   
     --free 
       (CASE
            WHEN round(nvl(b.bytes_free, 0) / 1024 / 1024/1024, 0) < 1 THEN
              round(nvl(b.bytes_free, 0) / 1024 / 1024, 0) || 'm'
            ELSE
              round(nvl(b.bytes_free, 0) / 1024 / 1024/1024, 0) || 'g'
       END )||' / '||
     --used
       (CASE
            WHEN round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024/1024, 0) < 1 THEN
              round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 0) ||'m'
            ELSE
              round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024/1024, 0) ||'g'
       END) || ' / ' ||
     --alloc
       (CASE
            WHEN round(a.bytes_alloc /1024/1024/1024, 0) < 1 THEN
              round(a.bytes_alloc /1024/1024 ,0)||'m'
            ELSE
              round(a.bytes_alloc /1024/1024/1024, 0)||'g'
       END) || ' / ' || 
     --limited  
       (CASE
           WHEN round(maxbytes/1024/1024/1024,0) < 1 THEN
             round(maxbytes/1024/1024,0)||'m'
           ELSE
             round(maxbytes/1024/1024/1024,0) ||'g'
        END ) "free/used/alloted/limited",
        100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100,0) "used/ALLOC%",
        round((a.bytes_alloc-nvl(b.bytes_free, 0))/maxbytes*100,0)   "used/MAX%"
from  ( select  f.tablespace_name,
               sum(f.bytes) bytes_alloc,
               sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
        from dba_data_files f
        group by tablespace_name) a,
      ( select  f.tablespace_name,
               sum(f.bytes)  bytes_free
        from dba_free_space f
        group by tablespace_name) b,
        dba_tablespaces t
where t.tablespace_name = a.tablespace_name
  AND a.tablespace_name = b.tablespace_name (+)
)
Select tablespace_name, /*"spc_used", Contents, status, extent_management,*/ "free/used/alloted/limited", "used/ALLOC%", "used/MAX%" From tbsstat
ORDER BY 3 desc;
赞(0) 打赏
未经允许不得转载:徐万新之路 » Oracle ts.sql

支持快讯、专题、百度收录推送、人机验证、多级分类筛选器,适用于垂直站点、科技博客、个人站,扁平化设计、简洁白色、超多功能配置、会员中心、直达链接、文章图片弹窗、自动缩略图等...

联系我们

觉得文章有用就打赏一下文章作者

非常感谢你的打赏,我们将继续提供更多优质内容,让我们一起创建更加美好的网络世界!

支付宝扫一扫

微信扫一扫

登录

找回密码

注册