自动去掉后台中指定的HINT
由于数据库版本升级,有些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(v_file); UTL_FILE.fclose(v_file_bak); raise;end;
执行该存储需要先建立名为EXPDIR的目录(directory),执行后在目录下找到replace_hint.sql该脚本,执行即可替换所有的HINT为空,如果不放心的话,可以使用文本比较工具先比较
replace_hint.sql和replace_hint_bak.sql,看两个文本的差别是否就是在HINT那.