Oracle 表碎片的监控与清理.doc

利用DBMS_SPACE包对Oracle 表碎片进行监控与清理 如果经常进行频繁的insert与delete操作，将会使表的HWM（高水位）变高，这样在作全表扫的操作时，将花费更多的成本。一般而言，当实际含有行数据的数据块只占HWM的50%时，我们一般认为表中数据块存在较大的浪费，出现这样的情况，一般建议对表进行重建。 对于查询某张表的空间使用情况，Oracle提供了一个包DBMS_SPACE来完成。关于该包的使用方法可以参见其说明。这里主要提供了一种快速布署的方法，来对数据库中的相关表进行监控，对浪费空间比较大的表进行表的重建，统计信息收集。其思路是，首先对数据库中所有用户的表作调查，确定哪些表需要监控，创建一张表来维护监控信息，碎片情况的收集通过Procedure来完成，如果有需要重建的表，则生成相关的语句，并将语句以邮件的方式发出。相关的脚本在HP-UX上运行，如果是其它平台，可作相应的修改。 在Oracle用户下创建相关的目录 mkdir /oracle/utils/tb_monitor mkdir /oracle/utils/tb_monitor/log mkdir /oracle/utils/tb_monitor/mail_result 创建TB_MONITOR表，由于这里是通过perfstat用户来进行监控，所以还需要显示的进行授权，方能在后面的procedure中查询dba_segments数据字典 TB_MONITOR表结构说明如下： TABLE_NAME：需要监控的表的名称。 OWNER：表所属用户。 SIZE_THRESHOLD：表的大小阈值 WASTE_THRESHOLD：浪费率的大小阈值，默认值为70%。 SGM_SPACE_MANAGEMENT：段管理方式，分为MANUAL和AUTO CURRENT_SIZE：表当前的大小。 CURRENT_WASTE：表当前的浪费率。 脚本如下： grant select on dba_segments to perfstat; conn perfstat/perfstat CREATE TABLE PERFSTAT.TB_MONITOR ( TABLE_NAME VARCHAR2(200) NOT NULL ENABLE, OWNER VARCHAR2(20) NOT NULL ENABLE, SIZE_THRESHOLD NUMBER, WASTE_THRESHOLD NUMBER, SGM_SPACE_MANAGEMENT VARCHAR2(6), CURRENT_SIZE NUMBER, CURRENT_WASTE NUMBER, CONSTRAINT PK_TAB_NAME PRIMARY KEY (TABLE_NAME,OWNER) USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 ); 完成表的创建后，赋予Public的查询权限，创建公共同义词。以方便查询： grant select on PERFSTAT.TB_MONITOR to public; create public synonym TB_MONITOR for PERFSTAT.TB_MONITOR; 确定需要监控的用户 查看监控会涉及到哪些用户，并筛选需要监控的表 set pagesize 20 select username from dba_users where username not in (SYS,SYSTEM,OUTLN,CTXSYS,DBSNMP,PERFSTAT,WMSYS,MDSYS,ORDSYS,ORDPLUGINS); 可以按照下面的语句进行筛选需要监控的表，生成插入的sql语句，并将这些需要监控的表插入监控表中，这里没有监控分区表。根据情况再加入一些筛选的条件。 另外，监控的范围是1M20G大小的普通表。 set feedback off set echo off set heading off set lines 300 set pagesize 50000 set trimspool on col segment_name Format a32 col Owner Format a12 spool table_monitor_insert.temp --select Owner,segment_name,sum(bytes/1024/1024) from dba_segments where segment_name in ( select insert into