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