当前位置:Gxlcms > mysql > 怎么监控oracle数据库表和表空间

怎么监控oracle数据库表和表空间

时间:2021-07-01 10:21:17 帮助过:29人阅读

怎么监控oracle数据库表和表空间的现状 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', 'TA

  怎么监控oracle数据库表和表空间的现状

  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')

人气教程排行