[oracle] 查看oracle数据库表空间使用率,定位数据size较大对象

举报
dber 发表于 2021/01/27 09:29:22 2021/01/27
【摘要】 =========================================1.查看表空间使用情况=========================================with ts as (SELECT d.tablespace_name "Name", d.status "Status",       TO_CHAR (NVL (a.MAXBYTES / 1024 / ...

=========================================
1.查看表空间使用情况
=========================================
with ts as (SELECT d.tablespace_name "Name", d.status "Status",
       TO_CHAR (NVL (a.MAXBYTES / 1024 / 1024 /1024, 0), '99,999,990.90') "Maxsize (G)",
       TO_CHAR (NVL (a.BYTES / 1024 / 1024 /1024, 0), '99,999,990.90') "Size (G)",
          TO_CHAR (NVL (a.BYTES - NVL (f.BYTES, 0), 0) / 1024 / 1024 /1024,
                   '99999999.99'
                  ) USE,
       TO_CHAR (NVL ((a.BYTES - NVL (f.BYTES, 0)) / a.MAXBYTES * 100, 0),
                '990.00'
               ) "Used %"
  FROM SYS.dba_tablespaces d,
       (SELECT   tablespace_name, SUM (BYTES) BYTES,SUM (greatest(BYTES,MAXBYTES)) MAXBYTES
            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 c.tablespace_name as name,'tempts',
to_char(c.maxbytes/1024/1024/1024,'99,999.999') "Maxsize (G)",
to_char(c.bytes/1024/1024/1024,'99,999.999') "Size (G)",
to_char(d.bytes_used/1024/1024/1024,'99,999.999') USE,
to_char(d.bytes_used*100/c.maxbytes,'99.99') "Used %"
from  (select tablespace_name,sum(bytes) bytes,sum(greatest(maxbytes,bytes)) maxbytes
from dba_temp_files GROUP by tablespace_name) c,
(select tablespace_name,sum(bytes_cached) bytes_used
from gv$temp_extent_pool GROUP by tablespace_name) d
where c.tablespace_name = d.tablespace_name)
select * from ts order by 6 desc;

 


=========================================
2.查看segment使用情况
=========================================
col segment_name for a50  
select tablespace_name,segment_name,round(sum(bytes/1024/1024/1024),1) as sizeG from  dba_segments where tablespace_name in ('OLS_DATA','OLS_LOB','OLS_BAT') group by segment_name,tablespace_name order by 3;

 

=========================================
3.查看具体表空间lob使用情况
=========================================


col segment_name for a50 
col table_name for a50  
select l.table_name,l.segment_name,round(sum(s.bytes/1024/1024/1024),1) as sizeG from  dba_segments s,dba_lobs l where s.segment_name = l.segment_name and s.tablespace_name = 'OLS_LOB' group by l.table_name,l.segment_name order by round(sum(s.bytes/1024/1024/1024),1);

【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。