在DataStage中使用APPEND,DELETE操作很容易形成高水位表，影响数仓报表和跑批的效率。自己写了一个清理高水位表的包。

 

CREATE OR REPLACE PACKAGE PAC_HIGH_WATER
AS
PROCEDURE P_HIGH_WATER;
END PAC_HIGH_WATER;

 

create or replace package body PAC_HIGH_WATER
as
PROCEDURE P_HIGH_WATER
as
   CURSOR table_cur IS
     SELECT table_name FROM USER_TABLES a ;
   table_rec table_cur%ROWTYPE;
   analy_table_str varchar2(4000);
   deal_table_str1 varchar2(4000);
   deal_table_str2 varchar2(4000);
   insert_table_deal varchar2(4000);
   high_water_level number(4) := 0;
BEGIN
    OPEN table_cur;
    LOOP
       FETCH table_cur into table_rec;
       EXIT WHEN table_cur%NOTFOUND;
       analy_table_str :=
       'SELECT round((1-a.used/b.num_total)*100,0) percent FROM
       (SELECT  COUNT (DISTINCT SUBSTR(rowid,1,15)) used,count(*) num FROM '|| table_rec.table_name ||') a,
       (SELECT segment_name,SUM(blocks) num_total FROM user_segments WHERE segment_name = '||chr(39)||table_rec.table_name||chr(39)||' GROUP BY segment_name ) b';
       execute immediate (analy_table_str) into high_water_level ;
       dbms_output.put_line(analy_table_str);
      
       if  high_water_level > 1
       then
       deal_table_str1 := 'alter table '|| table_rec.table_name || ' enable row movement';
       deal_table_str2 := 'alter table '|| table_rec.table_name || ' SHRINK SPACE';
       dbms_output.put_line(deal_table_str1);
       dbms_output.put_line(deal_table_str2);      

       execute immediate (deal_table_str1);
       execute immediate (deal_table_str2);
       insert_table_deal := 'INSERT INTO P_ODS_HIGHWATER_TABLE
       SELECT to_char(sysdate,'||chr(39)||'yyyymmdd'||chr(39)||'),segment_name,Used,num_total, round((1-a.used/b.num_total)*100,0) percent,a.num FROM
       (SELECT  COUNT (DISTINCT SUBSTR(rowid,1,15)) used,count(*) num FROM '|| table_rec.table_name ||') a,
       (SELECT segment_name,SUM(blocks) num_total FROM user_segments WHERE segment_name =' ||chr(39)||table_rec.table_name||chr(39)||' GROUP BY segment_name ) b';
       execute immediate (insert_table_deal);
       dbms_output.put_line(insert_table_deal);
      end if;

    END LOOP;
    CLOSE table_cur;

   commit;
end;
END PAC_HIGH_WATER;