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

小咪咪 2022-10-21 03:57 219阅读 0赞

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