Document ID : KB000095566
Last Modified Date : 15/05/2018
Show Technical Document Details
A how-to article on how to disable the automatic MQREORG and manually run it from the Database during off peak hours.
When it appears the Automic Workload Automation slows down during the UC_Reorg process, an option is to disable it and run manually from the DB during off peak hours.
1. Log in as the Admin in Client 0
3. Disable the automatic MQ_REORG by setting the MQ_CHECK_TIME=0
4. Have the DBA run this stored procedure manually from the database during off peak hours:

create or replace PROCEDURE UC_Reorg (v_table_name VARCHAR2 ) AS v_row_move VARCHAR2(30); v_tablespace VARCHAR2(30); v_assm VARCHAR2(30); v_coln VARCHAR2(255); v_seclob VARCHAR2(3); BEGIN SELECT ROW_MOVEMENT, Tablespace_name INTO v_row_move, v_tablespace FROM user_tables WHERE table_name = upper(v_table_name); SELECT segment_space_management INTO v_assm FROM user_tablespaces WHERE tablespace_name=v_tablespace; IF v_row_move = 'ENABLED' AND v_assm = 'AUTO' THEN EXECUTE immediate 'alter table ' || v_table_name || ' shrink space compact'; EXECUTE immediate 'alter table ' || v_table_name || ' shrink space'; begin select SECUREFILE into v_seclob from USER_LOBS where table_name = upper(v_table_name); Exception When NO_DATA_FOUND Then DBMS_OUTPUT.PUT_LINE('TABLE with no LOB -> OK'); v_seclob := 'YES'; End; if v_seclob = 'NO' then select COLUMN_NAME into v_coln from USER_TAB_COLS where table_name = upper(v_table_name) and data_type = 'BLOB'; EXECUTE immediate 'alter table ' || v_table_name || ' modify lob (' || v_coln || ') (shrink space)'; end if; END IF; END;;
Additional Information:
This stored procedure can be found in the DB folder of an AE installation image -> uc_ddl.sql