查看表空间每天增长和每周增长情况

查看数据每天变化量：
---查看前一天所有表空间的增长量
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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24500180/viewspace-1062905/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24500180/viewspace-1062905/