自动去掉后台中指定的HINT
自动去掉后台中指定的HINT
由于数据库版本升级,有些HINT已经没有效果(如/* +RULE */),决定从后台进行清理,但是存储+函数+触发器+视图一共有近2000的对象,一个个去SELECT之后,再替换为空,再去执行显然不太现实.故写了一个脚本实现自动替换.
[sql]
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), <span style="BACKGROUND-COLOR: #ff9632">UPPER(PHINT)</span>) > 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
&nbs
