用户想知道数据库每天增长几个G,到底都是啥?
1.查看每日表空间增长
Oracle查看表空间的每日增长量 - 墨天轮
2.查看每日用户占用增长
每天相同时间点查看,对比即可,可以写成定时任务输出报告
select owner,sum(bytes)/1024/1024/1024 GB from dba_segments
where owner not in('SYS','SYSTEM','OWBSYS_AUDIT','OWBSYS','APEX_PUBLIC_USER','APEX_030200','FLOWS_FILES','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','MDDATA','OLAPSYS','ORDDATA','ORDPLUGINS','SI_INFORMTN_SCHEMA','MDSYS','ORDSYS','XDB','ANONYMOUS','CTXSYS','EXFSYS','WMSYS','APPQOSSYS','DBSNMP','ORACLE_OCM','DIP','OUTLN','SYSMAN')
group by owner order by sum(bytes) desc;
2.查看每日用户对象占用增长
select owner,segment_name,segment_type,sum(bytes)/1024/1024 MB from dba_segments
where owner not in('SYS','SYSTEM','OWBSYS_AUDIT','OWBSYS','APEX_PUBLIC_USER','APEX_030200','FLOWS_FILES','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','MDDATA','OLAPSYS','ORDDATA','ORDPLUGINS','SI_INFORMTN_SCHEMA','MDSYS','ORDSYS','XDB','ANONYMOUS','CTXSYS','EXFSYS','WMSYS','APPQOSSYS','DBSNMP','ORACLE_OCM','DIP','OUTLN','SYSMAN')
group by owner,segment_name,segment_type order by sum(bytes) desc;
可以只查询top 20
select * from (select owner,segment_name,segment_type,sum(bytes)/1024/1024 MB from dba_segments
where owner not in('SYS','SYSTEM','OWBSYS_AUDIT','OWBSYS','APEX_PUBLIC_USER','APEX_030200','FLOWS_FILES','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','MDDATA','OLAPSYS','ORDDATA','ORDPLUGINS','SI_INFORMTN_SCHEMA','MDSYS','ORDSYS','XDB','ANONYMOUS','CTXSYS','EXFSYS','WMSYS','APPQOSSYS','DBSNMP','ORACLE_OCM','DIP','OUTLN','SYSMAN')
group by owner,segment_name,segment_type order by sum(bytes) desc) where rownum<21;