表空间增长量统计

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

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;

————————————————————————————————————————————————————————————

本人主要从事架构优化、系统建设、系统运维等方面的工作，专注于Oracle、Mysql、Redis、PG、OGG等技术和系统建设、运维、优化，欢迎留言或者加微信交流，转载请注明出处。

微信：deadknight9