ITPub博客

首页 > Linux操作系统 > Linux操作系统 > PL/SQL LOB和文件操作,bulk collect

PL/SQL LOB和文件操作,bulk collect

原创 Linux操作系统 作者:ljm0211 时间:2012-07-26 16:52:56 0 删除 编辑
create directory tmp_dir as '/tmp/gbs_sql';
grant read,write on directory tmp_dir to dbmgr;
CREATE TABLE gbs_sql
AS
   SELECT ROWNUM id,
          a.sql_id,
             REGEXP_REPLACE (a.sql_text,
                             '[' || CHR (128) || '-' || CHR (255) || ']',
                             'X')
          || ';'
             sql_text,
          DBMS_LOB.getlength (
                REGEXP_REPLACE (a.sql_text,
                                '[' || CHR (128) || '-' || CHR (255) || ']',
                                'X')
             || ';') sql_len
     FROM stage_sqlset a;
alter table gbs_sql add (dist300_hash integer);
alter table gbs_sql add (dist300 char(1));
update gbs_sql set dist300_hash=DBMS_UTILITY.GET_HASH_VALUE(dbms_lob.substr(sql_text,300),1,1000000000);
update gbs_sql set dist100_hash=DBMS_UTILITY.GET_HASH_VALUE(dbms_lob.substr(sql_text,100),1,1000000000);
alter table gbs_sql add (dist100_hash integer);
alter table gbs_sql add (dist100 char(1));
alter table gbs_sql add (sql_del_text clob);
update gbs_sql set sql_del_text=regexp_replace(sql_text,'\s+',' ');
alter table gbs_sql add (sql_del_len number);
update gbs_sql set sql_del_len=dbms_lob.getlength(sql_del_text);
alter table gbs_sql add (is_pl_sql char(1) default 'N');
create unique index uni_gbs_sql_id on gbs_sql(id);
create index ix_gbs_sql_dist300_hash on gbs_sql(dist300_hash);
create index ix_gbs_sql_dist100_hash on gbs_sql(dist100_hash);
create index ix_gbs_sql_len on gbs_sql(sql_len);
create index ix_gbs_sql_sql_id on gbs_sql(sql_id);
select dist300_hash,sql_len,--max(sql_len),min(sql_len),
max(id),min(id),max(sql_id),min(sql_id),count(*) from gbs_sql group by dist300_hash,sql_len order by count(*) desc;
select dist300_hash,max(sql_len),min(sql_len),
max(id),min(id),max(sql_id),min(sql_id),count(*) from gbs_sql group by dist300_hash order by count(*) desc;
exp dbmgr/killua2cow file=/tmp/gbs_sql/gbs_sql.dmp tables=dbmgr.gbs_sql log=/tmp/gbs_sql/gbs_sql.log
imp dbmgr/duan5lzh file=/tmp/gbs_sql.dmp full=y
exp dbmgr/killua2cow file=/tmp/gbs_sql/gbs_sql100.dmp tables=dbmgr.gbs_sql100 log=/tmp/gbs_sql/gbs_sql100.log
imp dbmgr/duan5lzh file=/tmp/gbs_sql100.dmp full=y
alter table dbmgr.gbs_sql add (sql_del_text_lower clob default empty_clob());
alter table dbmgr.gbs_sql add (give_up char(1) default 'X');
update gbs_sql set dist300='N',dist100='N';
update gbs_sql set dist300='Y' where id in (select min(id) from gbs_sql where is_pl_sql='N' group by dist300_hash);
update gbs_sql set dist100='Y' where id in (select min(id) from gbs_sql where is_pl_sql='N' group by dist100_hash);
create table gbs_sql100 as select id,sql_id,sql_text,sql_del_text,sql_del_text_lower,give_up FROM dbmgr.gbs_sql where dist100='Y' and is_pl_sql='N';
create unique index uni_gbs_sql100_id on gbs_sql100(id);
create unique index ix_gbs_gbs_sql100_id on gbs_sql100(sql_id);
-------------------------------------------------------
--create or replace procedure for_debug
--is
--
DECLARE
    type sub_sql_list is table of varchar2(600);
    type sql_id_list is table of varchar2(30);
   tab_sub_sql  sub_sql_list;
   tab_sql_id      sql_id_list;
   str_sub_sql  varchar2(600);
   str_sql_id      varchar2(30);
   flag             number;
   str_give_up char(1);
   num_resv_id number;
    v_limit number := 1000;
   
   CURSOR cur_sql
   IS
      SELECT sql_id, dbms_lob.substr(sql_del_text_lower,300) sub_sql
        FROM dbmgr.gbs_sql100;
BEGIN
   OPEN cur_sql;
   LOOP
      FETCH cur_sql bulk collect
      INTO tab_sql_id, tab_sub_sql limit v_limit;
      EXIT WHEN tab_sql_id.count=0;
      for i in tab_sql_id.first .. tab_sql_id.last loop
      --forall i in tab_sql_id.first .. tab_sql_id.
        str_sql_id := tab_sql_id(i);
        str_sub_sql := tab_sub_sql(i);
          select give_up into str_give_up from dbmgr.gbs_sql100 where sql_id=str_sql_id;
          if str_give_up = 'X' then
              select count(*),min(id) into flag,num_resv_id from dbmgr.gbs_sql100 where sql_id<>str_sql_id and give_up<>'Y' and dbms_lob.instr(sql_del_text_lower,str_sub_sql)>0;
             
              if flag > 0 then
                update dbmgr.gbs_sql100 set give_up='Y' where sql_id=str_sql_id and give_up<>'N';
                update dbmgr.gbs_sql100 set give_up='N' where id=num_resv_id;
              else
                update dbmgr.gbs_sql100 set give_up='N' where sql_id=str_sql_id;
              end if;
          end if;
      --execute immediate 'update dbmgr.gbs_sql a set a.give_up=''Y'' where a.sql_id=''' || str_sql_id ||''' and exists (select * from dbmgr.gbs_sql b where b.sql_id<>a.sql_id and b.give_up<>''Y'' and dbms_lob.instr(b.sql_del_text_lower,'''|| str_sub_str ||''')>0)';
      end loop;
      commit;
   END LOOP;
   close cur_sql;
END;
-------------------------------------------------------
create or replace procedure for_debug
is
--DECLARE
    type sub_sql_list is table of varchar2(600);
    type sql_id_list is table of varchar2(30);
   tab_sub_sql  sub_sql_list;
   tab_sql_id      sql_id_list;
   str_sub_sql  varchar2(600);
   str_sql_id      varchar2(30);
   flag             number;
   str_give_up char(1);
   num_resv_id number;
    v_limit number := 1000;
   
   CURSOR cur_sql
   IS
      SELECT sql_id, dbms_lob.substr(sql_del_text_lower,300) sub_sql
        FROM dbmgr.gbs_sql where dist100='Y' and is_pl_sql='N';
BEGIN
   OPEN cur_sql;
   LOOP
      FETCH cur_sql bulk collect
      INTO tab_sql_id, tab_sub_sql limit v_limit;
      EXIT WHEN cur_sql%NOTFOUND;
      for i in tab_sql_id.first .. tab_sql_id.last loop
      --forall i in tab_sql_id.first .. tab_sql_id.
        str_sql_id := tab_sql_id(i);
        str_sub_sql := tab_sub_sql(i);
          select give_up into str_give_up from dbmgr.gbs_sql where sql_id=str_sql_id;
          if str_give_up = 'X' then
              select count(*),max(id) into flag,num_resv_id from dbmgr.gbs_sql where sql_id<>str_sql_id and give_up<>'Y' and dbms_lob.instr(sql_del_text_lower,str_sub_sql)>0;
             
              if flag > 0 then
                update dbmgr.gbs_sql set give_up='Y' where sql_id=str_sql_id and give_up<>'N';
                update dbmgr.gbs_sql set give_up='N' where id=num_resv_id;
              else
                update dbmgr.gbs_sql set give_up='N' where sql_id=str_sql_id;
              end if;
          end if;
      --execute immediate 'update dbmgr.gbs_sql a set a.give_up=''Y'' where a.sql_id=''' || str_sql_id ||''' and exists (select * from dbmgr.gbs_sql b where b.sql_id<>a.sql_id and b.give_up<>''Y'' and dbms_lob.instr(b.sql_del_text_lower,'''|| str_sub_str ||''')>0)';
      end loop;
   END LOOP;
   close cur_sql;
END;
--update gbs_sql set give_up='X';
--select count(*),max(sql_id) from gbs_sql where give_up='N';
-------------------------------------------------------
/* Formatted on 2012/7/3 14:18:40 (QP5 v5.200.12012.37730) */
--CREATE OR REPLACE PROCEDURE for_debug
--IS
   --
   DECLARE
   lob_sql         CLOB;
   lob_sql_lower   CLOB;
   len             NUMBER;
   loop_count      NUMBER;
   str_sql         VARCHAR2 (32767);
   amount_v        NUMBER := 10000;
   amount          NUMBER;
   position        NUMBER;
   data_buffer     VARCHAR2 (32767);
   str_sql_id      VARCHAR2 (30);
   CURSOR cur_sql
   IS
      SELECT sql_id, sql_del_text, sql_del_text_lower
        FROM dbmgr.gbs_sql
      FOR UPDATE;
BEGIN
   OPEN cur_sql;
   LOOP
      FETCH cur_sql
      INTO str_sql_id, lob_sql, lob_sql_lower;
      EXIT WHEN cur_sql%NOTFOUND;
      len := DBMS_LOB.getlength (lob_sql);
      position := 1;
      --IF len <= 30000
      --THEN
      --   str_sql := DBMS_LOB.SUBSTR (lob_sql, len);
      --   str_sql := LOWER (str_sql);
      --   --lob_sql_lower := str_sql;
      --   DBMS_LOB.writeappend (lob_sql_lower, len, str_sql);
      --ELSE
         amount := amount_v;
         loop_count := CEIL (len / amount);
         dbms_output.put_line('sql_id:'||str_sql_id);
         --execute immediate 'update dbmgr.gbs_sql set sql_del_text_lower=empty_clob() where sql_id='''||str_sql_id||'''';
         --dbms_lob.createtemporary(temp_lob,true,dbms_lob.session);
         FOR i IN 1 .. loop_count
         LOOP           
            amount := amount_v;
            position := (i - 1) * amount + 1;
            DBMS_LOB.read (lob_sql,
                           amount,
                           position,
                           data_buffer);
            data_buffer := LOWER (data_buffer);
            DBMS_LOB.writeappend (lob_sql_lower, amount, data_buffer);
            data_buffer := NULL;
         END LOOP;
      --END IF;
   --big_amount := big_amount_v;
   --loop_count := CEIL ((len+1-position) / big_amount);
   END LOOP;
   close cur_sql;
END;
-------------------------------------------------------
-------------------------------------------------------
/* Formatted on 2012/7/3 9:32:58 (QP5 v5.200.12012.37730) */
DECLARE
   lob_sql       CLOB;
   len           NUMBER;
   amount_v      NUMBER := 1;
   amount        NUMBER;
   position      NUMBER;
   data_buffer   VARCHAR2 (4);
   str_sql_id    VARCHAR2 (30);
   CURSOR cur_sql
   IS
      SELECT sql_id, sql_del_text FROM dbmgr.gbs_sql;
BEGIN
   OPEN cur_sql;
   LOOP
      FETCH cur_sql
      INTO str_sql_id, lob_sql;
      EXIT WHEN cur_sql%NOTFOUND;
      len := DBMS_LOB.getlength (lob_sql);
      position := 1;
      amount := amount_v;
     <>
      LOOP
         DBMS_LOB.read (lob_sql,
                        amount,
                        position,
                        data_buffer);
         IF REGEXP_INSTR (data_buffer, '\s') > 0
         THEN
            position := position + 1;
         ELSE
            EXIT inner_loop;
         END IF;
         data_buffer := NULL;
      END LOOP inner_loop;
      --big_amount := big_amount_v;
      --loop_count := CEIL ((len+1-position) / big_amount);
      IF position > 1
      THEN
         --temp_lob := EMPTY_CLOB ();
         DBMS_LOB.COPY (lob_sql,
                        lob_sql,
                        len + 1 - position,
                        1,
                        position);
         DBMS_LOB.TRIM (lob_sql,
                        len + 1 - position);
         --lob_sql := temp_lob;
      END IF;
   END LOOP;
   close cur_sql;
END;
-------------------------------------------------------
declare
lob_sql clob;
len number;
amount number := 1;
position number;
data_buffer varchar2(4);
str_sql_id varchar2(30);
cursor cur_sql is
 select sql_id,sql_text from dbmgr.stage_sqlset;
begin
open cur_sql;
loop
fetch cur_sql into str_sql_id, lob_sql;
exit when cur_sql%NOTFOUND;
len := dbms_lob.getlength(lob_sql);
position := len;
<>
loop
dbms_lob.read(lob_sql,amount,position, data_buffer);
if regexp_instr(data_buffer,'\s') <= 0 then
    exit inner_loop;
else
    position := position -1;
end if;
end loop inner_loop;
if data_buffer = ';' then
    execute immediate 'update dbmgr.gbs_sql set is_pl_sql=''Y'' where sql_id=''' || str_sql_id || '''';
    --dbms_output.put_line(str_sql_id);
end if;
end loop;
close cur_sql;
end;
declare
len number;
len_del number;
i number :=0;
str_sql_id varchar2(30);
lob_sql clob;
lob_del_sql clob;
cursor cur_sql is
    select sql_id,sql_text,sql_del_text from dbmgr.gbs_sql where is_pl_sql='Y' for update;
begin
    open cur_sql;
    loop
    fetch cur_sql into str_sql_id,lob_sql,lob_del_sql;
    exit when cur_sql%NOTFOUND;
   
    i := i + 1;
    len := DBMS_LOB.getlength(lob_sql);
    len_del := dbms_lob.getlength(lob_del_sql);
   
    dbms_lob.trim(lob_sql, len-1);
    dbms_lob.trim(lob_del_sql, len_del-1);
    end loop;
    close cur_sql;
    dbms_output.put_line(i);
    close cur_sql;
end;
update dbmgr.gbs_sql set sql_len=dbms_lob.getlength(sql_text),sql_del_len=dbms_lob.getlength(sql_del_text) where is_pl_sql='Y';
-------------------------------------------------------
/* Formatted on 2012/6/30 17:21:54 (QP5 v5.200.12012.37730) */
CREATE OR REPLACE PROCEDURE for_debug
IS
   --DECLARE
   amount_v        NUMBER := 10000;
   amount          NUMBER;
   file_maxsize    NUMBER := 4000000;
   num_sqlpfile    NUMBER := 1500;
   data_buffer     VARCHAR2 (32767);
   loop_count      NUMBER;
   lob_sql_text    CLOB;
   str_sql_id      VARCHAR2 (30);
   num_sql_len     NUMBER;
   fno             NUMBER;
   file_len        NUMBER;
   position        NUMBER;
   num_sql         NUMBER;
   is_exit_inner   BOOLEAN := FALSE;
   file_handle     UTL_FILE.file_type;
   CURSOR cur_sql
   IS
      SELECT sql_id, sql_text FROM dbmgr.gbs_sql;
BEGIN
   --file_handle := utl_file.fopen('TMP_DIR','t.sql','a',32767);
   OPEN cur_sql;
   fno := 0;
  <>
   LOOP
      fno := fno + 1;
      file_handle :=
         UTL_FILE.fopen ('TMP_DIR',
                         'gbs_sql_' || fno || '.sql',
                         'w',
                         32767);
      file_len := 0;
     <>
      FOR i IN 1 .. num_sqlpfile
      LOOP
         FETCH cur_sql
         INTO str_sql_id, lob_sql_text;
         ---------------------------------------------------------
         IF cur_sql%NOTFOUND
         THEN
            IF is_exit_inner OR num_sql = num_sqlpfile
            THEN
               UTL_FILE.FREMOVE ('TMP_DIR', 'gbs_sql_' || fno || '.sql');
            END IF;
            EXIT outer_loop;
         END IF;
         is_exit_inner := FALSE;
         ---------------------------------------------------------
         --EXIT outer_loop WHEN cur_sql%NOTFOUND;
         --dbms_output.put_line(str_sql_id||':'||length(lob_sql_text));
         num_sql_len := DBMS_LOB.getlength (lob_sql_text);
         amount := amount_v;
         loop_count := CEIL (num_sql_len / amount);
         FOR j IN 1 .. loop_count
         LOOP
            position := (j - 1) * amount + 1;
            DBMS_LOB.read (lob_sql_text,
                           amount,
                           position,
                           data_buffer);
            UTL_FILE.put (file_handle, data_buffer);
            --UTL_FILE.fflush(file_handle);
            data_buffer := NULL;
         END LOOP;
         file_len := file_len + num_sql_len;
         IF file_len >= file_maxsize
         THEN
            is_exit_inner := TRUE;
            EXIT inner_loop;
         END IF;
         UTL_FILE.new_line (file_handle);
         --UTL_FILE.fflush(file_handle);
         file_len := file_len + 1;
         --IF utl_file.is_open(file_handle) THEN
         --  utl_file.put_line(file_handle,lob_sql_text);
         --end IF;
         num_sql := i;
      END LOOP inner_loop;
     
   --UTL_FILE.fflush(file_handle);
      UTL_FILE.fclose (file_handle);
      UTL_FILE.frename ('TMP_DIR',
                        'gbs_sql_' || fno || '.sql',
                        'TMP_DIR',
                        'gbs_sql_' || fno || '_' || num_sql || '.sql');
   END LOOP outer_loop;
  close cur_sql;
   IF UTL_FILE.is_open (file_handle)
   THEN
     --UTL_FILE.fflush(file_handle);
      UTL_FILE.fclose (file_handle);
      IF num_sql <> num_sqlpfile
      THEN
         UTL_FILE.frename ('TMP_DIR',
                           'gbs_sql_' || fno || '.sql',
                           'TMP_DIR',
                           'gbs_sql_' || fno || '_' || num_sql || '.sql');
      END IF;
   END IF;
--dbms_output.put_line('t:'||t);
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line ('str_sql_id:' || str_sql_id);
         DBMS_OUTPUT.put_line (SQLERRM);
         IF UTL_FILE.is_open (file_handle)
         THEN
            UTL_FILE.fclose (file_handle);
         END IF;
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;
END;

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

下一篇: 忆、情、缘
请登录后发表评论 登录
全部评论

注册时间:2009-05-14

  • 博文量
    272
  • 访问量
    442408