oracle查询数据增长列,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