查看表空间的增长情况

最新推荐文章于 2024-07-03 09:14:51 发布

congzhirou8448

最新推荐文章于 2024-07-03 09:14:51 发布

阅读量209

点赞数

首先查看表空间的增长情况：

查询前一周表空间增长情况：

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;

查询前一天表空间增长情况：

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 OWNER,SEGMENT_NAME,SEGMENT_TYPE,bytes/1024/1024/1024 GB from dba_segments where TABLESPACE_NAME =' TABLESPACE_NAME ' order by bytes desc;

select * from (select OWNER,SEGMENT_NAME,SEGMENT_TYPE,bytes/1024/1024/1024 GB from dba_segments where TABLESPACE_NAME ='TABLESPACE_NAME' order by bytes desc ) where rownum<30;

如何确定某些对象容量增涨快，需要一定时间的跟踪，周期性查询该对象的容量。然后根据对象做相应后续处理。

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

转载于:http://blog.itpub.net/30491527/viewspace-2120607/