ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 清理高水位表脚本

清理高水位表脚本

原创 Linux操作系统 作者:sys_tzs 时间:2012-02-27 23:39:41 0 删除 编辑
在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;
 
 
 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25146649/viewspace-717261/,如需转载,请注明出处,否则将追究法律责任。

上一篇: tkprof使用
请登录后发表评论 登录
全部评论

注册时间:2012-02-05

  • 博文量
    4
  • 访问量
    2147