Manual MQ_REORG

Document ID : KB000095566
Last Modified Date : 30/05/2018
Show Technical Document Details
Introduction:
A how-to article on how to disable the automatic MQREORG and manually run it from the Database during off peak hours.
Background:
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.
Instructions:
1. Log in as the Admin in Client 0
2. Edit the UC_SYSTEM_SETTINGS
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