利用DBMS_SPACE包对Oracle 表碎片进行监控与清理

你的名字 2022-06-07 14:17 217阅读 0赞

如果经常进行频繁的insert与delete操作，将会使表的HWM（高水位）变高，这样在作全表扫的操作时，将花费更多的成本。一般而言，当实际含有行数据的数据块只占HWM的50%时，我们一般认为表中数据块存在较大的浪费，出现这样的情况，一般建议对表进行重建。

对于查询某张表的空间使用情况，Oracle提供了一个包DBMS\_SPACE来完成。关于该包的使用方法可以参见其说明。这里主要提供了一种快速布署的方法，来对数据库中的相关表进行监控，对浪费空间比较大的表进行表的重建，统计信息收集。其思路是，首先对数据库中所有用户的表作调查，确定哪些表需要监控，创建一张表来维护监控信息，碎片情况的收集通过Procedure来完成，如果有需要重建的表，则生成相关的语句，并将语句以邮件的方式发出。相关的脚本在Linux上运行，如果是其它平台，可作相应的修改。

一．     在Oracle用户下创建相关的目录

orcl:/home/oracle@oracle>mkdir /u01/monitor  
orcl:/home/oracle@oracle>mkdir /u01/monitor/log  
orcl:/home/oracle@oracle>mkdir /u01/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：表当前的浪费率。

脚本如下：

SQL> create user perfstat identified by perfstat;  
  
User created.  
  
SQL> grant select on dba\_segments to perfstat;  
  
Grant succeeded.

SQL> grant connect,resource to perfstat;  
  
Grant succeeded.

Synonym created.

SQL> grant select on  dba\_tables to perfstat;  
  
Grant succeeded.  
  
SQL> grant select on dba\_tablespaces to perfstat;  
  
Grant succeeded.

SQL> GRANT SELECT ON SYS.DBA\_INDEXES TO PERFSTAT;  
  
Grant succeeded.  
  
SQL> GRANT SELECT ON SYS.DBA\_INDEXES TO PERFSTAT;  
  
Grant succeeded.  
  
SQL> GRANT SELECT ON SYS.DBA\_INDEXES TO PERFSTAT;  
  
Grant succeeded.  
  
SQL> GRANT SELECT ON SYS.DBA\_TABLESPACES TO PERFSTAT;  
  
Grant succeeded.

创建表：  
 CREATE TABLE"PERFSTAT"."TB\_MONITOR"

(   "TABLE\_NAME" VARCHAR2(200) NOT NULL ENABLE,

"OWNER" VARCHAR2(20) NOT NULLENABLE,

"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 MAXTRANS255

);

完成表的创建后，赋予Public的查询权限，创建公共同义词。以方便查询：

SQL> grant select on "PERFSTAT"."TB\_MONITOR" to public;  
  
Grant succeeded.  
  
SQL> create public synonym TB\_MONITOR for "PERFSTAT"."TB\_MONITOR";

三．     确定需要监控的表

可以按照下面的语句进行筛选需要监控的表，生成插入的sql语句，并将这些需要监控的表插入监控表中，这里没有监控分区表。根据情况再加入一些筛选的条件。

另外，监控的范围是1M---20G大小的普通表。

select 'insert into PERFSTAT.TB\_MONITOR(owner,TABLE\_NAME,CURRENT\_SIZE)  values( '||''''||Owner||''','||''''||segment\_name||''''||','||sumsize||');'  
 from(  
   select Owner,segment\_name,sum(bytes/1024/1024) sumsize from dba\_segments where segment\_name in  
     (  
     select table\_name from user\_tables  
    where  
    Owner='HAGJ'  
    and table\_name not in (select table\_name from user\_part\_tables)  
    and table\_name not like '%BAK%' ) Group by Owner,segment\_name  
having sum(bytes/1024/1024) between 1 and 200000 order by 3)

生成的sql语句类似如下：

insert into PERFSTAT.TB\_MONITOR(owner,TABLE\_NAME,CURRENT\_SIZE)  values( 'HAGJ','BSSFCK',1);  
insert into PERFSTAT.TB\_MONITOR(owner,TABLE\_NAME,CURRENT\_SIZE)  values( 'HAGJ','PROMGOODS',2);  
insert into PERFSTAT.TB\_MONITOR(owner,TABLE\_NAME,CURRENT\_SIZE)  values( 'HAGJ','KMATTACHMENT',2);  
insert into PERFSTAT.TB\_MONITOR(owner,TABLE\_NAME,CURRENT\_SIZE)  values( 'HAGJ','PROMOTIONS',2);  
insert into PERFSTAT.TB\_MONITOR(owner,TABLE\_NAME,CURRENT\_SIZE)  values( 'HAGJ','DAYTYPECOLL',2);  
......

数据插入到监控到以后，作一番检查和更新：

更新空间的浪费阀值为70%：

update TB\_MONITORset WASTE\_THRESHOLD=70;

commit;

更新段空间管理方式：

update TB\_MONITORc set SGM\_SPACE\_MANAGEMENT=(select b.SEGMENT\_SPACE\_MANAGEMENT from dba\_tablesa,dba\_tablespaces b

wherea.tablespace\_name=b.tablespace\_name and a.owner=c.owner

anda.table\_name=c.table\_name);

commit;

查看是否都已经更新完毕：

select \* fromTB\_MONITOR where SGM\_SPACE\_MANAGEMENT is null;

delete  TB\_MONITOR where SGM\_SPACE\_MANAGEMENT isnull;

四．     创建监控的procedure：

CREATE OR REPLACE procedure PERFSTAT.p\_tb\_monitor Authid Current\_User is  
    /\*  
       Introduce  
       ================  
       This procedure is use for monitor misc table which is need to be rebuild.  
       can be use on 9iR2 or upper version,MSSM or ASSM both can be use.  
       Written by 1157758655@qq.com  
        
       ---------------  
    \*/  
    cursor c\_tab\_mon\_man is select table\_name,owner,sgm\_space\_management from perfstat.TB\_MONITOR  
    where SGM\_SPACE\_MANAGEMENT='MANUAL';  
    /\*debug\*/  
    --and rownum<5;  
    cursor c\_tab\_mon\_aut is select table\_name,owner,sgm\_space\_management from perfstat.TB\_MONITOR  
    where SGM\_SPACE\_MANAGEMENT='AUTO';  
      /\*debug\*/  
    --and rownum<5;  
    v\_tab\_mon\_man c\_tab\_mon\_man%rowtype;  
    v\_tab\_mon\_aut c\_tab\_mon\_aut%rowtype;  
    a number;  
    v\_tab\_name varchar2(200);  
    v\_owner varchar2(20);  ----new variable  
    v\_seg\_mng varchar2(200);  
    v\_free\_blocks number;  
    V\_TOTAL\_BLOCKS                   NUMBER;  
    V\_TOTAL\_BYTES                    NUMBER;  
    V\_UNUSED\_BLOCKS                  NUMBER;  
    V\_UNUSED\_BYTES                   NUMBER;  
    V\_LAST\_USED\_EXTENT\_FILE\_ID       NUMBER;  
    V\_LAST\_USED\_EXTENT\_BLOCK\_ID      NUMBER;  
    V\_LAST\_USED\_BLOCK                NUMBER;  
    V\_UNFORMATTED\_BLOCKS             NUMBER;  
    V\_UNFORMATTED\_BYTES              NUMBER;  
    V\_FS1\_BLOCKS                     NUMBER;  
    V\_FS1\_BYTES                      NUMBER;  
    V\_FS2\_BLOCKS                     NUMBER;  
    V\_FS2\_BYTES                      NUMBER;  
    V\_FS3\_BLOCKS                     NUMBER;  
    V\_FS3\_BYTES                      NUMBER;  
    V\_FS4\_BLOCKS                     NUMBER;  
    V\_FS4\_BYTES                      NUMBER;  
    V\_FULL\_BLOCKS                    NUMBER;  
    V\_FULL\_BYTES                     NUMBER;  
  
   err\_num varchar2(2000);  
  
   begin  
     open c\_tab\_mon\_man;  
     loop  
     fetch c\_tab\_mon\_man into v\_tab\_mon\_man;  
     if v\_tab\_mon\_man.sgm\_space\_management='MANUAL' then  
     DBMS\_SPACE.FREE\_BLOCKS(v\_tab\_mon\_man.owner, v\_tab\_mon\_man.table\_name,'TABLE',0,v\_free\_blocks);  
     DBMS\_SPACE.UNUSED\_SPACE(v\_tab\_mon\_man.owner, v\_tab\_mon\_man.table\_name,'TABLE',V\_TOTAL\_BLOCKS,  
     V\_TOTAL\_BYTES,V\_UNUSED\_BLOCKS,V\_UNUSED\_BLOCKS,V\_LAST\_USED\_EXTENT\_FILE\_ID,V\_LAST\_USED\_EXTENT\_BLOCK\_ID,  
     V\_LAST\_USED\_BLOCK);  
     update perfstat.TB\_MONITOR a set CURRENT\_WASTE=round((v\_free\_blocks/(V\_TOTAL\_BLOCKS)\*100),2)  
     where SGM\_SPACE\_MANAGEMENT='MANUAL' and a.TABLE\_NAME=v\_tab\_mon\_man.table\_name and a.owner=v\_tab\_mon\_man.owner;  
     commit;  
     end if;  
     exit when c\_tab\_mon\_man%NOTFOUND;  
     end loop;  
     close c\_tab\_mon\_man;  
  
     open c\_tab\_mon\_aut;  
     loop  
     fetch c\_tab\_mon\_aut into v\_tab\_mon\_aut;  
     if v\_tab\_mon\_aut.sgm\_space\_management='AUTO' then  
     DBMS\_SPACE.UNUSED\_SPACE(v\_tab\_mon\_aut.owner, v\_tab\_mon\_aut.table\_name,'TABLE',V\_TOTAL\_BLOCKS,  
     V\_TOTAL\_BYTES,V\_UNUSED\_BLOCKS,V\_UNUSED\_BLOCKS,V\_LAST\_USED\_EXTENT\_FILE\_ID,V\_LAST\_USED\_EXTENT\_BLOCK\_ID,  
     V\_LAST\_USED\_BLOCK);  
     DBMS\_SPACE.SPACE\_USAGE(v\_tab\_mon\_aut.owner, v\_tab\_mon\_aut.table\_name,'TABLE',V\_UNFORMATTED\_BLOCKS,  
     V\_UNFORMATTED\_BYTES,V\_FS1\_BLOCKS,V\_FS1\_BYTES,V\_FS2\_BLOCKS,V\_FS2\_BYTES,V\_FS3\_BLOCKS,V\_FS3\_BYTES,  
     V\_FS4\_BLOCKS,V\_FS4\_BYTES,V\_FULL\_BLOCKS,V\_FULL\_BYTES);  
     update perfstat.TB\_MONITOR a  
     set CURRENT\_WASTE=round(((V\_FS1\_BLOCKS+V\_FS2\_BLOCKS+V\_FS3\_BLOCKS+V\_FS4\_BLOCKS)/V\_TOTAL\_BLOCKS)\*100,2)  
     where SGM\_SPACE\_MANAGEMENT='AUTO' and a.TABLE\_NAME=v\_tab\_mon\_aut.table\_name and a.owner=v\_tab\_mon\_aut.owner;  
     commit;  
     end if;  
     exit when c\_tab\_mon\_aut%NOTFOUND;  
     end loop;  
     close c\_tab\_mon\_aut;  
  
     update perfstat.TB\_MONITOR a set CURRENT\_SIZE=(select sum(bytes)/1024/1024 as size\_m from dba\_segments  
     where segment\_name=A.TABLE\_NAME and owner=a.owner and segment\_type LIKE 'TABLE%');  
     COMMIT;  
   
       
     exception  
      when others then  
               err\_num := SQLCODE;  
               if err\_num = 1 then  
               dbms\_output.put\_line(SQLERRM(err\_num));  
              end if;  
   
    end;  
/

执行上述procedure创建脚本，如果需要手工编译过程，执行以下命令：

alter procedureperfstat.p\_tb\_monitor compile;

五．     创建构建碎片整理的sql语句的脚本：tb\_monitor\_construct.sql

orcl:/u01/monitor@oracle>vi tb\_monitor\_construct.sql  
  
\----construct sql for move tables：  
set feedback off;  
set pages 100;  
set heading on;  
col table\_name for a30;  
set trimspool on;  
set line 500  
col script\_for\_gather\_stat for a250  
select 'alter table ' ||owner||'.'||table\_name||' move;'  
from  
perfstat.TB\_MONITOR WHERE SGM\_SPACE\_MANAGEMENT IS NOT NULL AND  
(  
(SIZE\_THRESHOLD IS NOT NULL AND SIZE\_THRESHOLD<CURRENT\_SIZE AND WASTE\_THRESHOLD<CURRENT\_WASTE)  
or  
(SIZE\_THRESHOLD is null and WASTE\_THRESHOLD<CURRENT\_WASTE)  
);  
\---after the sql to move tables be constructed,we need to construct sql for rebuild the indexes:  
\------表Move后，需要重建索引，以下是手工构建重建的语句：  
\------关于重建索引的并行度问题，如果在rebuild的时候指定并行度，那么，rebuild完成后最后将并行度改回来。  
\------alter index test.IDX\_T99 noparallel;  
\-----否则，将倾向于走全表扫  
select 'alter index '||owner||'.'||index\_name||' rebuild;' from dba\_indexes a where  (a.table\_name,a.owner) in  
(  
SELECT table\_name,owner FROM TB\_MONITOR WHERE SGM\_SPACE\_MANAGEMENT IS NOT NULL AND  
(  
(SIZE\_THRESHOLD IS NOT NULL AND SIZE\_THRESHOLD<CURRENT\_SIZE AND WASTE\_THRESHOLD<CURRENT\_WASTE)  
or  
(SIZE\_THRESHOLD is null and WASTE\_THRESHOLD<CURRENT\_WASTE)  
)  
);  
\--------now, we construct sql for gather statistics for the tables:  
\--------the percent is up to the size of table such as:  
\--------表重建完毕后，需要收集一次统计信息  
\----不同的表采用不同的分析采样：  
\--if size\_m<=500 then  estimate\_percent=>100  
\--if size\_m between 500 and 1024  then estimate\_percent=>50  
\--if size\_m between 1024 and 5120  then estimate\_percent=>10  
\--if size\_m between 5120 and 10240  then estimate\_percent=>3  
\--if size\_m between 10240 and 20480   then estimate\_percent=>1  
\--可以采用如下的语句一次性生成分析语句  
select  'exec dbms\_stats.gather\_table\_stats(OWNNAME=>'||''''||upper(owner)||''',tabname=>'||''''||  
upper(table\_name)||''',cascade=>TRUE,degree=>4);' as script\_for\_gather\_stat  
from (  
select owner,segment\_name table\_name, sum(bytes) / 1024 / 1024 size\_m  
  from dba\_segments  
 where (owner,segment\_name) in  
       (  
SELECT owner,table\_name FROM perfstat.TB\_MONITOR WHERE SGM\_SPACE\_MANAGEMENT IS NOT NULL AND  
(  
(SIZE\_THRESHOLD IS NOT NULL AND SIZE\_THRESHOLD<CURRENT\_SIZE AND WASTE\_THRESHOLD<CURRENT\_WASTE)  
or  
(SIZE\_THRESHOLD is null and WASTE\_THRESHOLD<CURRENT\_WASTE)  
)  
) group by segment\_name,owner  
 order by size\_m) a  
 where a.size\_m<=500---------------------<500M  
 union all  
select  'exec dbms\_stats.gather\_table\_stats(OWNNAME=>'||''''||upper(owner)||''',tabname=>'||''''||  
upper(table\_name)||''',cascade=>TRUE,estimate\_percent=>50,degree=>4);' as script\_for\_gather\_stat  
from (  
select owner,segment\_name table\_name, sum(bytes) / 1024 / 1024 size\_m  
  from dba\_segments  
 where (owner,segment\_name) in  
       (  
SELECT owner,table\_name FROM perfstat.TB\_MONITOR WHERE SGM\_SPACE\_MANAGEMENT IS NOT NULL AND  
(  
(SIZE\_THRESHOLD IS NOT NULL AND SIZE\_THRESHOLD<CURRENT\_SIZE AND WASTE\_THRESHOLD<CURRENT\_WASTE)  
or  
(SIZE\_THRESHOLD is null and WASTE\_THRESHOLD<CURRENT\_WASTE)  
)  
)  
           group by segment\_name,owner  
 order by size\_m) a  
 where a.size\_m between 500 and 1024 --------500M-1G  
  union all  
select  'exec dbms\_stats.gather\_table\_stats(OWNNAME=>'||''''||upper(owner)||''',tabname=>'||''''||  
upper(table\_name)||''',cascade=>TRUE,estimate\_percent=>10,degree=>4);' as script\_for\_gather\_stat  
from (  
select owner,segment\_name table\_name, sum(bytes) / 1024 / 1024 size\_m  
  from dba\_segments  
 where (owner,segment\_name) in  
       (  
SELECT owner,table\_name FROM perfstat.TB\_MONITOR WHERE SGM\_SPACE\_MANAGEMENT IS NOT NULL AND  
(  
(SIZE\_THRESHOLD IS NOT NULL AND SIZE\_THRESHOLD<CURRENT\_SIZE AND WASTE\_THRESHOLD<CURRENT\_WASTE)  
or  
(SIZE\_THRESHOLD is null and WASTE\_THRESHOLD<CURRENT\_WASTE)  
)  
)  
           group by segment\_name,owner  
 order by size\_m) a  
where a.size\_m between 1024 and 5120-----------1G-5G  
  union all  
select  'exec dbms\_stats.gather\_table\_stats(OWNNAME=>'||''''||upper(owner)||''',tabname=>'||''''||  
upper(table\_name)||''',cascade=>TRUE,estimate\_percent=>3,degree=>4);' as script\_for\_gather\_stat  
from (  
select owner,segment\_name table\_name, sum(bytes) / 1024 / 1024 size\_m  
  from dba\_segments  
 where (owner,segment\_name) in  
       (  
SELECT owner,table\_name FROM perfstat.TB\_MONITOR WHERE SGM\_SPACE\_MANAGEMENT IS NOT NULL AND  
(  
(SIZE\_THRESHOLD IS NOT NULL AND SIZE\_THRESHOLD<CURRENT\_SIZE AND WASTE\_THRESHOLD<CURRENT\_WASTE)  
or  
(SIZE\_THRESHOLD is null and WASTE\_THRESHOLD<CURRENT\_WASTE)  
)  
)  
           group by segment\_name,owner  
 order by size\_m) a  
where a.size\_m between 5120 and 10240-----------5G-10G  
  union all  
select  'exec dbms\_stats.gather\_table\_stats(OWNNAME=>'||''''||upper(owner)||''',tabname=>'||''''||  
upper(table\_name)||''',cascade=>TRUE,estimate\_percent=>1,degree=>4);' as script\_for\_gather\_stat  
from (  
select owner,segment\_name table\_name, sum(bytes) / 1024 / 1024 size\_m  
  from dba\_segments  
 where (owner,segment\_name) in  
       (  
SELECT owner,table\_name FROM perfstat.TB\_MONITOR WHERE SGM\_SPACE\_MANAGEMENT IS NOT NULL AND  
(  
(SIZE\_THRESHOLD IS NOT NULL AND SIZE\_THRESHOLD<CURRENT\_SIZE AND WASTE\_THRESHOLD<CURRENT\_WASTE)  
or  
(SIZE\_THRESHOLD is null and WASTE\_THRESHOLD<CURRENT\_WASTE)  
)  
)  
           group by segment\_name,owner  
 order by size\_m) a  
where a.size\_m >10240;

六．     创建shell脚本来调用上述procedure进行监控

\#!/bin/sh  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\#                  DB TUNING MONITOR SCRIPT                             \#  
\#      MUST run the initial scrpit at the first time                    \#  
\#            to create procedure and table                              \#  
\#    2017-10-19   writen by 1157758655@qq.com                       \#  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
. ~oracle/.bash\_profile  \#如果执行过程中有问题，可以单独export ORACLE\_HOME  
\# Path Define  
work\_path=/u01/monitor  
log=$\{work\_path\}/log  
mail\_result=$\{work\_path\}/mail\_result  
cd $work\_path  
\# SENDMAIL  Define  
DBALIST="1157758655@qq.com"  
  
\#Define ORACLE\_SID  
orasid=$ORACLE\_SID  
  
\#date\_format\_yyyymmdd  
date\_yyyymmdd=\`date '+%Y%m%d'\`  
  
\#\#\# CHECH TABLE IF NEED TO BE REBUILD START \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
sqlplus "/ as sysdba"<<EOF>/dev/null  
set feedback on;  
set pages 10000;  
set line 250;  
set echo on;  
set heading on;  
set trimspool on  
col owner for a12;  
col table\_name for a30;  
col SIZE\_THRESHOLD for 999999  
col WASTE\_THRESHOLD for 999  
exec perfstat.p\_tb\_monitor;  
spool tb\_need\_to\_rebuild.txt  
SELECT \* FROM TB\_MONITOR WHERE SGM\_SPACE\_MANAGEMENT IS NOT NULL AND  
(  
(SIZE\_THRESHOLD IS NOT NULL AND SIZE\_THRESHOLD<CURRENT\_SIZE AND WASTE\_THRESHOLD<CURRENT\_WASTE)  
or  
(SIZE\_THRESHOLD is null and WASTE\_THRESHOLD<CURRENT\_WASTE)  
);  
spool off  
exit  
EOF  
  
\#check if need to send alert mail  
sqlplus -s "/ as sysdba"<<EOF>/dev/null  
set feedback off  
set pages 0  
set head off  
set echo off  
spool mail\_flag.tmp  
SELECT count(\*) FROM TB\_MONITOR WHERE SGM\_SPACE\_MANAGEMENT IS NOT NULL AND  
(  
(SIZE\_THRESHOLD IS NOT NULL AND SIZE\_THRESHOLD<CURRENT\_SIZE AND WASTE\_THRESHOLD<CURRENT\_WASTE)  
or  
(SIZE\_THRESHOLD is null and WASTE\_THRESHOLD<CURRENT\_WASTE)  
);  
spool off  
\----to construct sql for the tables which need to rebuild;  
spool rebuild.tmp  
@tb\_monitor\_construct.sql  
spool off  
!echo "SQL for move tables:">rebuild.sql;  
!echo "================================= ">>rebuild.sql;  
!cat rebuild.tmp |grep move |grep -v SQL >>rebuild.sql;  
!echo "================================= ">>rebuild.sql;  
!echo "SQL for rebuild indexes:">>rebuild.sql;  
!echo "================================= ">>rebuild.sql;  
!cat rebuild.tmp |grep rebuild |grep -v SQL >>rebuild.sql;  
!echo "================================= ">>rebuild.sql;  
!echo "SQL for gather statistics:">>rebuild.sql;  
!echo "================================= ">>rebuild.sql;  
!cat rebuild.tmp |grep dbms\_stats |grep -v SQL |grep -v select >>rebuild.sql;  
exit  
EOF  
read mail\_flag<mail\_flag.tmp  
\#clear tmp file  
rm -f \*.tmp  
  
\#\#\#\#\#\#\#\#\#\#\#\#\#\# CHECH TABLE IF NEED TO BE REBUILD END \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
  
\#\#\#\#\#\#\#\#\#\#\#\#\#\# Send the result to dba START \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
echo "=================  $orasid DB TUNING MONITOR  REPORT =================">mail\_$\{date\_yyyymmdd\}.mail  
cat tb\_need\_to\_rebuild.txt>>mail\_$\{date\_yyyymmdd\}.mail  
echo "============ FOLLOWING SQL TO MOVING,REBUIDING,ANALYZING=========">>mail\_$\{date\_yyyymmdd\}.mail  
cat rebuild.sql>>mail\_$\{date\_yyyymmdd\}.mail  
  
echo "============================ REPORT END ==========================">>mail\_$\{date\_yyyymmdd\}.mail  
rm -f tb\_need\_to\_rebuild.txt  
rm -f rebuild.sql  
if \[ $mail\_flag -ge 1 \]  
then  
mailx -s "$\{orasid\} TUNING MONITOR  REPORT(SOME TABLE NEED TO REBUILD!!)" -a mail\_$\{date\_yyyymmdd\}.mail $DBALIST < mail\_$\{date\_yyyymmdd\}.mail  
else echo "empty"  
fi  
mv -f mail\_$\{date\_yyyymmdd\}.mail $\{mail\_result\}

首次执行过程后。对浪费空间为0的表进行清理，不需要进行监控

delete fromTB\_MONITOR where CURRENT\_WASTE=0;

七．     定时监控：

为了能自动执行表碎片监控的脚本，将脚本加到crontab列表中：

0 0 \* \* \*  /u01/monitor/monitor.sh

注意：如果没有安装mailx 请先安装mailx