监控oracle表和表空间的现状和趋势

1. Segment Space Management (ASSM)表的使用情况

declare

  unf   number;

  unfb  number;

  fs1   number;

  fs1b  number;

  fs2   number;

  fs2b  number;

  fs3  number;

  fs3b  number;

  fs4   number;

  fs4b  number;

  full  number;

  fullb number;

begin

  dbms_space.space_usage('&1',

                         '&2',

                         'TABLE',

                         unf,

                         unfb,

                         fs1,

                         fs1b,

                         fs2,

                         fs2b,

                         fs3,

                         fs3b,

                         fs4,

                         fs4b,

                         full,

                         fullb);

  dbms_output.put_line('unformatted_blocks:' || unf);

  dbms_output.put_line('full_blocks(0%     free):' || full);

  dbms_output.put_line('fs1_blocks(0-25%   free):' || fs1);

  dbms_output.put_line('fs2_blocks(25-50%  free):' || fs2);

  dbms_output.put_line('fs3_blocks(50-75%  free):' || fs3);

  dbms_output.put_line('fs4_blocks(75-100% free):' || fs4);

end;

/
2. 表空间的使用历史记录(只输出了每天的第一次统计结果)
select b.name,
       a.rtime,
       a.tablespace_usedsize,
       a.tablespace_size,
       round(100 * a.tablespace_usedsize / a.tablespace_size) used_percent
  from dba_hist_tbspc_space_usage a,
       (select t2.name,
               min(rtime) rtime,
               min(tablespace_id) tablespace_id
          from dba_hist_tbspc_space_usage t1
         inner join v$tablespace t2 on t1.tablespace_id = t2.TS#
         where t2.NAME = upper('&1')
         group by name, substr(rtime,1,10)
) b
 where a.tablespace_id = b.tablespace_id
   and a.rtime = b.rtime
order by a.rtime;
3. 表的剩余空间预测
select * from table(dbms_space.OBJECT_GROWTH_TREND('ECC_CC', 'YTKC_ORDERRELEVANCE', 'TABLE'))
参考资料:

 

Comments are closed.