ITPub博客

首页 > 数据库 > Oracle > 查看表空间每天增长和每周增长情况

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

Oracle 作者:liuhaimiao 时间:2016-02-26 09:33:58 0 删除 编辑

查看数据每天变化量:
---查看前一天所有表空间的增长量
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/223653/viewspace-1994887/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
sap技术顾问

注册时间:2014-07-29

  • 博文量
    933
  • 访问量
    774576