代码的大意是将一些输出放到bde_validate_structure&&schema&&table_name&&index_name..sql这样一个文件里。标红色的地方的输出会大于1m。serveroutput的最大值就是1M,也就是说DBMS_OUTPUT最大只能输出1M。大家帮看看有什么方法突破这个限制或改写成直接输出到一个文件而不用DBMS_OUTPUT。
SPOOL bde_validate_structure&&schema&&table_name&&index_name..sql;
DECLARE
v_sql VARCHAR2(1000);
v_dbversion v$instance.version%TYPE;
CURSOR c1 IS
SELECT owner,
index_name
FROM all_indexes
WHERE table_owner LIKE RTRIM(UPPER('&&schema'))||'%'
AND table_name LIKE RTRIM(UPPER('&&table_name'))||'%'
AND index_name LIKE '%'||RTRIM(UPPER('&&index_name'))||'%'
AND table_owner <> 'SYS'
AND owner <> 'SYS'
AND table_owner <> 'SYSTEM'
AND owner <> 'SYSTEM'
ORDER BY
owner,
index_name;
BEGIN
SELECT version INTO v_dbversion FROM v$instance;
v_sql:='/*$Header: bde_validate_structure'||
'&&schema&&table_name&&index_name..sql '||
'(8.0-9.2) '||TO_CHAR(sysdate,'YYYY/MM/DD')||
' gen by bde_rebuild.sql csierra bde $*/';
DBMS_OUTPUT.PUT_LINE(v_sql);
v_sql:='SET echo on feed on;';
DBMS_OUTPUT.PUT_LINE(v_sql);
v_sql:='SPOOL bde_validate_structure&&schema&&table_name&&index_name..txt;';
DBMS_OUTPUT.PUT_LINE(v_sql);
:v_count:=0;
FOR c1_rec IN c1 LOOP
:v_count:=:v_count+1;
v_sql:='ANALYZE INDEX '||c1_rec.owner||'.'||c1_rec.index_name||
' VALIDATE STRUCTURE;';
DBMS_OUTPUT.PUT_LINE(v_sql);
v_sql:='INSERT INTO bde_index_stats '||
'SELECT ixs.*, '''||c1_rec.owner||''', '''||c1_rec.index_name||''', '||
'sysdate, '||:v_count||
' FROM index_stats ixs;';
DBMS_OUTPUT.PUT_LINE(v_sql);
END LOOP;
v_sql:='COMMIT;';
DBMS_OUTPUT.PUT_LINE(v_sql);
v_sql:='SPOOL off;';
DBMS_OUTPUT.PUT_LINE(v_sql);
v_sql:='SET echo off feed off;';
DBMS_OUTPUT.PUT_LINE(v_sql);
END;
/
SPOOL off;