由于数据库版本升级,有些HINT已经没有效果(如/* +RULE */),决定从后台进行清理,但是存储+函数+触发器+视图一共有近2000的对象,一个个去SELECT之后,再替换为空,再去执行显然不太现实.故写了一个脚本实现自动替换.

CREATE OR REPLACE PROCEDURE AUTOREPLACEHINT(PHINT VARCHAR2)--实现自动替换所有存储、函数、视图、触发器中的 指定HINT为空(格式为/* +HINT */),并备份原脚本
  v_file_bak UTL_FILE.file_type;
  v_file     UTL_FILE.file_type;
  v_lob    clob;
  v_text   VARCHAR2(30000);
  V_Buffer VARCHAR2(30000);
  Amount   BINARY_INTEGER := 30000;
  AM_TMP   INTEGER := 30000;
  i        INTEGER := 1;
  v_count  INTEGER := 0;

begin
  v_file_bak := UTL_FILE.fopen('EXPDIR', 'replace_hint_bak.sql', 'w'); --创建备份文件
  v_file := UTL_FILE.fopen('EXPDIR', 'replace_hint.sql', 'w'); --创建文件
  <<xloop>> 
  for x in (select username
              from dba_users
             where ACCOUNT_STATUS = 'OPEN'
               AND INITIAL_RSRC_CONSUMER_GROUP != 'SYS_GROUP'
               AND USERNAME <> 'SYSMAN')
              
  LOOP
    <<yloop>> 
    FOR Y IN (select  DISTINCT NAME,TYPE
                from dba_source
               WHERE OWNER = x.username
                 AND TYPE IN ('PROCEDURE','FUNCTION','TRIGGER','VIEW'))
    LOOP
       <<TTloop>>
      FOR TT IN (select  TEXT
                from dba_source
               WHERE OWNER = X.USERNAME AND NAME=Y.NAME
                 AND TYPE=Y.TYPE
               order by line)
    LOOP
      --这里使用正则表达式来判断是否找到要替换的HINT会更好\更通用,我正则技术不过关就没写正则了。。。
      IF instr(UPPER(TT.TEXT), UPPER(PHINT)) > 0 AND instr(TT.TEXT, '/*') > 0 and instr(TT.TEXT, '+') > 0 THEN
        DBMS_OUTPUT.put_line(Y.TYPE||' '||X.USERNAME||'.'||Y.NAME);
        v_count:=v_count+1;
        --原来的备份
        SELECT DBMS_METADATA.get_ddl(y.type, Y.NAME, X.USERNAME)
          INTO V_LOB
          FROM DUAL;
      
        Amount := DBMS_LOB.getlength(V_LOB);
        IF Amount < 30000 THEN
          DBMS_LOB.READ(V_LOB, Amount, i, V_Buffer);
        else
          LOOP
            DBMS_LOB.READ(V_LOB, AM_TMP, i, V_Buffer);
            UTL_FILE.PUTF(v_file_bak, V_Buffer);
            Amount := Amount - 30000;
            i      := i + 30000;
            EXIT WHEN Amount < 30000;
          END LOOP;
          DBMS_LOB.READ(V_LOB, Amount, i, V_Buffer);
        END IF;
        UTL_FILE.PUTF(v_file_bak, V_Buffer);
        UTL_FILE.PUTF(v_file_bak, '/');
        UTL_FILE.new_line(v_file_bak);
      
        --形成新的
        for z in (select text
                    from dba_source
                   where name = y.name
                     and type = y.type
                     and owner = x.username
                   order by line)
        LOOP
          
          if instr(upper(z.text), y.name) > 0 AND instr(upper(z.text), y.type) > 0 then
            
          if instr(upper(z.text), y.type) > 0 then
            
            UTL_FILE.PUTF(v_file,'CREATE OR REPLACE '||
                          replace(upper(z.text),
                                  y.name,
                                  x.username ||'"."'|| y.name));
              END IF;
           continue;
          end if;
          IF instr(UPPER(z.text), UPPER(PHINT)) > 0 AND instr(z.text, '/*') > 0 THEN
            v_text := SUBSTR(z.text,
                             instr(z.text, '/*'),
                             instr(z.text, '*/') - instr(z.text, '/*') + 2);
            v_text:=replace(z.text,v_text);
            UTL_FILE.PUTF(v_file, v_text);
          ELSE
            UTL_FILE.PUTF(v_file, z.text);
          END IF;
        
        END LOOP;
        UTL_FILE.PUTF(v_file, chr(10)||'/');
        UTL_FILE.new_line(v_file);
        EXIT TTloop;
      END IF;
      END LOOP;
    END LOOP;
  END LOOP;
  UTL_FILE.fclose(v_file);
  UTL_FILE.fclose(v_file_bak);
  
  dbms_output.put_line('一共修改了对象:'||v_count||'个');
exception
  when others then
    UTL_FILE.fclose(