Release: Automic Workload Automation-Base Edition 12.x and 21.0.x
Component: Automation Engine
Configuration
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_
v_upper_table := upper(v_
begin
EXECUTE immediate
'alter session set ddl_
exception
WHEN others THEN
dbms_output.put_line('
end;
begin
SELECT row_movement,
tablespace_name,
iot_type
INTO v_row_move,
v_tablespace,
v_iot
FROM user_tables
WHERE table_name = v_
exception
WHEN others THEN
dbms_output.put_line('
v_row_move := 'DISABLED';
v_tablespace := '';
end;
if v_iot IS NULL THEN
BEGIN
SELECT segment_space_
INTO v_assm
FROM user_tablespaces
WHERE tablespace_name=v_
exception
WHEN others THEN
dbms_output.put_line('
v_assm := 'NONE';
end;
endIF;IF v_row_move = '
AND
v_assm = 'AUTO'
AND
v_iot IS NULL then
BEGIN
EXECUTE immediate
'alter table ' || v_upper_
exception
WHEN no_lock_granted THEN
dbms_output.put_line('ALTER
v_long_lock := 'YES';
when others THEN
dbms_output.put_line('ALTER
v_long_lock := 'YES';
end;ENDIF;IF v_iot = 'IOT'
AND
v_long_lock = 'NO' then
BEGIN
EXECUTE immediate
'alter table ' || v_upper_
exception
WHEN others THEN
dbms_output.put_line('ALTER
end;ENDIF;IF v_row_move = '
AND
v_assm = 'AUTO'
AND
v_long_lock = 'NO' then
BEGIN
EXECUTE immediate
'alter table ' || v_upper_
exception
WHEN others THEN
dbms_output.put_line('ALTER
end;BEGIN
EXECUTE immediate
'alter table ' || v_upper_
exception
WHEN others THEN
dbms_output.put_line('ALTER
END;BEGIN
SELECT securefile
INTO v_seclob
FROM user_lobs
WHERE table_name = v_upper_
exception
WHEN no_data_found THEN
v_seclob := 'YES';
when others THEN
dbms_output.put_line('TABLE
v_seclob := 'YES';
END;IF v_seclob = 'NO' then
FOR a IN
(
SELECT column_name
FROM user_tab_cols
WHERE table_name = v_
AND data_type = '
loop
BEGIN
EXECUTE immediate
'alter table ' || v_table_
exception
WHEN others THEN
dbms_output.put_line('ALTER
end;END
loop;ENDIF;ENDIF;BEGIN
EXECUTE immediate
'alter session set ddl_
exception
WHEN others THEN
dbms_output.put_line('
END;END;;