oracle查看某个表空间数据增长量,查看表空间每天增长和每周增长情况

查看数据每天变化量：

---查看前一天所有表空间的增长量

select C.tablespace_name,

D."Total(MB)",

D."Used(MB)" - C."Used(MB)" AS "Increment(MB)",

to_char(trunc(sysdate - 1),'yyyy/mm/dd') "TIME"

from (select B.name tablespace_name,

case when B.name not like 'UNDO%' then round(A.tablespace_size * 8 / 1024)

when B.name like 'UNDO%' then round(A.tablespace_size * 8 / 1024 / 2)

END as "Total(MB)",

round(A.tablespace_usedsize*8 / 1024) "Used(MB)",

A.rtime

from DBA_HIST_TBSPC_SPACE_USAGE A, v$tablespace B

where A.tablespace_id = B.TS#

and to_char(to_date(replace(rtime, '/', null),

'mmddyyyy hh24:mi:ss'),

'yyyymmdd hh24:mi') =

to_char(trunc(sysdate - 1), 'yyyymmdd hh24:mi')) C,

(select B.name tablespace_name,

case when B.name not like 'UNDO%' then round(A.tablespace_size * 8 / 1024)

when B.name like 'UNDO%' then round(A.tablespace_size * 8 / 1024 / 2)

END as "Total(MB)",

round(A.tablespace_usedsize*8 / 1024) "Used(MB)",

A.rtime

from DBA_HIST_TBSPC_SPACE_USAGE A, v$tablespace B

where A.tablespace_id = B.TS#

and to_char(to_date(replace(rtime, '/', null),

'mmddyyyy hh24:mi:ss'),

'yyyymmdd hh24:mi') =

to_char(trunc(sysdate), 'yyyymmdd hh24:mi')) D

where C.tablespace_name = D.tablespace_name;

---查看前一周所有表空间的增长量

select C.tablespace_name,

D."Total(MB)",

D."Used(MB)" - C."Used(MB)" AS "Increment(MB)",

to_char(next_day(trunc(sysdate),2)-7,'yyyy/mm/dd')||'--'||to_char(next_day(trunc(sysdate),2)-7,'yyyy/mm/dd') "TIME"

from (select B.name tablespace_name,

case when B.name not like 'UNDO%' then round(A.tablespace_size * 8 / 1024)

when B.name like 'UNDO%' then round(A.tablespace_size * 8 / 1024 / 2)

END as "Total(MB)",

round(A.tablespace_usedsize*8 / 1024) "Used(MB)",

A.rtime

from DBA_HIST_TBSPC_SPACE_USAGE A, v$tablespace B

where A.tablespace_id = B.TS#

and to_char(to_date(replace(rtime, '/', null),

'mmddyyyy hh24:mi:ss'),

'yyyymmdd hh24:mi') =

to_char(next_day(trunc(sysdate),2)-14,'yyyymmdd hh24:mi')) C,

(select B.name tablespace_name,

case when B.name not like 'UNDO%' then round(A.tablespace_size * 8 / 1024)

when B.name like 'UNDO%' then round(A.tablespace_size * 8 / 1024 / 2)

END as "Total(MB)",

round(A.tablespace_usedsize*8 / 1024) "Used(MB)",

A.rtime

from DBA_HIST_TBSPC_SPACE_USAGE A, v$tablespace B

where A.tablespace_id = B.TS#

and to_char(to_date(replace(rtime, '/', null),

'mmddyyyy hh24:mi:ss'),

'yyyymmdd hh24:mi') =

to_char(next_day(trunc(sysdate),2)-7,'yyyymmdd hh24:mi')) D

where C.tablespace_name = D.tablespace_name;