REM ================================================================================== REM Name: aqcoalesce.sql REM Author: Oracle MetaLink note #271855.1 (entitled "Procedure To Manually REM Coalesce All IOTs/Indexes Associated With AQ Tables") REM REM Description: REM Please read the MetaLink note before using this procedure. REM REM Modifications: REM TGorman 30apr09 - added "debug mode" code and calls to DBMS_APPLICATION_INFO REM ================================================================================== set echo on feedback on timing on spool aqcoalesce create or replace procedure aqcoalesce (in_debug_mode in boolean default FALSE) as -- v_rebuild_statement VARCHAR2(1000); err_msg VARCHAR2(100); -- cursor c1 is select owner, queue_table from dba_queue_tables where recipients='SINGLE' and owner NOT IN ('SYSTEM') and (compatible LIKE '8.%' or compatible LIKE '10.%'); -- cursor c2 is select owner, queue_table from dba_queue_tables where recipients='MULTIPLE' and (compatible LIKE '8.1%' or compatible LIKE '10.%'); -- cursor c3 is select owner, queue_table from dba_queue_tables where recipients='MULTIPLE' and compatible LIKE '8.0%'; -- cursor c4 is select owner, index_name from dba_indexes where table_name in ('DEF$_AQCALL','DEF$_AQERROR') and index_type= 'NORMAL'; -- TABLE_NOTEXIST EXCEPTION; PRAGMA EXCEPTION_INIT(TABLE_NOTEXIST,-942); -- BEGIN -- if in_debug_mode = FALSE then -- sys.dbms_system.ksdwrt(1,'AQ coalesce procedure starting execution at ' || to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')); -- end if; -- dbms_application_info.set_module('AQCOALESCE','Starting phase 1'); BEGIN -- FOR c1_rec IN c1 LOOP -- v_rebuild_statement := 'ALTER INDEX '||c1_rec.owner||'.AQ$_'||c1_rec.queue_table||'_I REBUILD COMPUTE STATISTICS'; dbms_output.put_line(v_rebuild_statement); if in_debug_mode = FALSE then -- sys.dbms_system.ksdwrt(1,'Executing '||v_rebuild_statement||'.'); EXECUTE IMMEDIATE v_rebuild_statement; -- end if; -- v_rebuild_statement := 'ALTER INDEX '||c1_rec.owner||'.AQ$_'||c1_rec.queue_table||'_T REBUILD COMPUTE STATISTICS'; dbms_output.put_line(v_rebuild_statement); if in_debug_mode = FALSE then -- sys.dbms_system.ksdwrt(1,'Executing '||v_rebuild_statement||'.'); EXECUTE IMMEDIATE v_rebuild_statement; -- end if; -- END LOOP; -- EXCEPTION WHEN OTHERS THEN -- if in_debug_mode = FALSE then -- sys.dbms_system.ksdwrt(1,'AQ coalesce procedure ended execution unsuccessfully at ' || to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')); -- end if; -- err_msg := SUBSTR(SQLERRM,1,100); raise_application_error(-20001,err_msg); -- END; -- dbms_application_info.set_action('Starting phase 2'); BEGIN -- FOR c2_rec IN c2 LOOP -- v_rebuild_statement := 'ALTER TABLE '||c2_rec.owner||'.AQ$_'||c2_rec.queue_table||'_I COALESCE'; dbms_output.put_line(v_rebuild_statement); if in_debug_mode = FALSE then -- sys.dbms_system.ksdwrt(1,'Executing '||v_rebuild_statement||'.'); EXECUTE IMMEDIATE v_rebuild_statement; -- end if; -- v_rebuild_statement := 'ALTER TABLE '||c2_rec.owner||'.AQ$_'||c2_rec.queue_table||'_T COALESCE'; dbms_output.put_line(v_rebuild_statement); if in_debug_mode = FALSE then -- sys.dbms_system.ksdwrt(1,'Executing '||v_rebuild_statement||'.'); EXECUTE IMMEDIATE v_rebuild_statement; -- end if; -- v_rebuild_statement := 'ALTER TABLE '||c2_rec.owner||'.AQ$_'||c2_rec.queue_table||'_H COALESCE'; dbms_output.put_line(v_rebuild_statement); if in_debug_mode = FALSE then -- sys.dbms_system.ksdwrt(1,'Executing '||v_rebuild_statement||'.'); EXECUTE IMMEDIATE v_rebuild_statement; -- end if; -- BEGIN -- --The spillover IOT AQ$_.._D will only exist when spillover occurs when using buffered messages --either in a Streams environment or in an AQ buffered message environment. --This object may therefore not exist so we handle the situation in the exception handler. -- v_rebuild_statement := 'ALTER TABLE '||c2_rec.owner||'.AQ$_'||c2_rec.queue_table||'_D COALESCE'; dbms_output.put_line(v_rebuild_statement); if in_debug_mode = FALSE then -- sys.dbms_system.ksdwrt(1,'Executing '||v_rebuild_statement||'.'); EXECUTE IMMEDIATE v_rebuild_statement; -- end if; -- EXCEPTION -- WHEN TABLE_NOTEXIST THEN NULL; -- END; -- END LOOP; -- EXCEPTION -- WHEN OTHERS THEN -- if in_debug_mode = FALSE then -- sys.dbms_system.ksdwrt(1,'AQ coalesce procedure ended execution unsuccessfully at ' || to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')); -- end if; -- err_msg := SUBSTR(SQLERRM,1,100); raise_application_error(-20002,err_msg); -- END; -- dbms_application_info.set_action('Starting phase 3'); BEGIN -- FOR c3_rec IN c3 LOOP -- v_rebuild_statement := 'ALTER TABLE '||c3_rec.owner||'.AQ$_'||c3_rec.queue_table||'_I COALESCE'; dbms_output.put_line(v_rebuild_statement); if in_debug_mode = FALSE then -- sys.dbms_system.ksdwrt(1,'Executing '||v_rebuild_statement||'.'); EXECUTE IMMEDIATE v_rebuild_statement; -- end if; -- END LOOP; -- EXCEPTION -- WHEN OTHERS THEN -- if in_debug_mode = FALSE then -- sys.dbms_system.ksdwrt(1,'AQ coalesce procedure ended execution unsuccessfully at ' || to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')); -- end if; -- err_msg := SUBSTR(SQLERRM,1,100); raise_application_error(-20003,err_msg); -- END; -- dbms_application_info.set_action('Starting phase 4'); BEGIN -- FOR c4_rec IN c4 LOOP -- v_rebuild_statement := 'ALTER INDEX '||c4_rec.owner||'.'||c4_rec.index_name||' REBUILD COMPUTE STATISTICS'; dbms_output.put_line(v_rebuild_statement); if in_debug_mode = FALSE then -- sys.dbms_system.ksdwrt(1,'Executing '||v_rebuild_statement||'.'); EXECUTE IMMEDIATE v_rebuild_statement; -- end if; -- END LOOP; -- EXCEPTION -- WHEN OTHERS THEN -- if in_debug_mode = FALSE then -- sys.dbms_system.ksdwrt(1,'AQ coalesce procedure ended execution unsuccessfully at ' || to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')); -- end if; -- err_msg := SUBSTR(SQLERRM,1,100); raise_application_error(-20004,err_msg); -- END; -- if in_debug_mode = FALSE then -- sys.dbms_system.ksdwrt(1,'AQ coalesce procedure ended successful execution at '|| to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')); -- end if; dbms_application_info.set_action('Completed.'); -- EXCEPTION -- WHEN OTHERS THEN -- if in_debug_mode = FALSE then -- sys.dbms_system.ksdwrt(1,'AQ coalesce procedure ended execution unsuccessfully at ' || to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')); -- end if; -- err_msg := SUBSTR(SQLERRM,1,100); raise_application_error(-20005,err_msg); -- END; / show errors spool off set echo off feedback 6 timing off