oracle 数据库对象增长记录查询对比语句

select *
  from (select a.owner,
               a.segment_name,
               a.tablespace_name,
               b.megas - a.megas diff
          from (select owner, segment_name, tablespace_name, sum(megas) megas
                  from object_rd
                 where to_char(recode_time, 'yyyymmdd') = '20120627'
                 group by owner, segment_name, tablespace_name) a,
               (select owner, segment_name, tablespace_name, sum(megas) megas
                  from object_rd
                 where to_char(recode_time, 'yyyymmdd') = '20120628'
                 group by owner, segment_name, tablespace_name) b
         where a.owner = b.owner(+)
           and a.segment_name = b.segment_name(+)
              --and a.segment_type=b.segment_type
           --and a.tablespace_name = b.tablespace_name
           union
          select b.owner,
               b.segment_name,
               b.tablespace_name,
               b.megas - a.megas diff
          from (select owner, segment_name, tablespace_name, sum(megas) megas
                  from object_rd
                 where to_char(recode_time, 'yyyymmdd') = '20120626'
                 group by owner, segment_name, tablespace_name) a,
               (select owner, segment_name, tablespace_name, sum(megas) megas
                  from object_rd
                 where to_char(recode_time, 'yyyymmdd') = '20120627'
                 group by owner, segment_name, tablespace_name) b
         where a.owner(+) = b.owner
           and a.segment_name(+) = b.segment_name
              --and a.segment_type=b.segment_type
           --and a.tablespace_name = b.tablespace_name
           ) c
 where c.diff > 1 or c.diff is null

order by c.diff desc

------------------------------------------------------------------------------------------------------------------------

select owner,segment_name,tablespace_name,segment_type,diff,flag
  from (select a.owner,
               a.segment_name,
               a.tablespace_name,
               a.segment_type,
               nvl(b.megas - a.megas,a.megas) diff,
               nvl2(b.megas - a.megas,'原有对象','新增或已删除对象') flag
          from (select owner, segment_name,segment_type, tablespace_name, sum(megas) megas
                  from object_rd
                 where to_char(record_time, 'yyyymmdd') = '20130220'
                 group by owner, segment_name,segment_type, tablespace_name) a,
               (select owner, segment_name,segment_type, tablespace_name, sum(megas) megas
                  from object_rd
                 where to_char(record_time, 'yyyymmdd') = '20130221'
                 group by owner, segment_name, segment_type,tablespace_name) b
         where a.owner = b.owner(+)
           and a.segment_name = b.segment_name(+)
              and a.segment_type=b.segment_type(+)
           --and a.tablespace_name = b.tablespace_name
           union
          select b.owner,
               b.segment_name,
               b.tablespace_name,
                b.segment_type,
              nvl(b.megas - a.megas,b.megas) diff,
               nvl2(b.megas - a.megas,'原有对象','新增或已删除对象') flag
          from (select owner, segment_name,segment_type, tablespace_name, sum(megas) megas
                  from object_rd
                 where to_char(record_time, 'yyyymmdd') = '20130220'
                 group by owner, segment_name, segment_type,tablespace_name) a,
               (select owner, segment_name,segment_type, tablespace_name, sum(megas) megas
                  from object_rd
                 where to_char(record_time, 'yyyymmdd') = '20130221'
                 group by owner, segment_name, segment_type,tablespace_name) b
         where a.owner(+) = b.owner
           and a.segment_name(+) = b.segment_name
              and a.segment_type(+)=b.segment_type
           --and a.tablespace_name = b.tablespace_name
           ) c
 where c.diff > 0
 and segment_name not like '%=$0'
 and not exists(select 1 from dba_recyclebin t where t.owner=c.owner and t.original_name=c.segment_name)
order by c.diff desc