rem SYNOPSYS
rem
rem   @purge_mass_executions.sql start_date end_date
rem
rem NAME
rem
rem   purge_mass_executions.sql - SQLPlus Purge Audit execution data by date range
rem
rem USAGE
rem
rem   start_date     :- e.g. '01-Jan-2008'         - Start of Date Range
rem
rem   end_date       :- e.g. '02-Jan-2008'         - End of Date  Range
rem
rem RETURNS
rem
rem   none
rem
rem DESCRIPTION
rem
rem   This SQL*Plus script can be called from the SQL*Plus shell, and is for deleting mass audit execution data with performance consideration
rem
rem   This script is designed to be run from a repository owner (for 11g before) or a WorkspaceOwner and the workspace is already set.
rem
rem   The start date and end date parameters, should be in string type.
rem
rem NOTE - designed for 11g or later now, if 10g or before, check the lines marked with "paris" and do some replacement there. 
rem        check bug 11878076 for more details (sometimes, not necessary to disable all related constraints)
rem

set serveroutput on
set verify off

CREATE OR REPLACE FUNCTION get_min_delay
    return number
  is
    l_min_delay number(10);
  begin
    l_min_delay := 10;  -- default value : no entry under 10 minutes old may be purged

    begin
      select to_number(property_value)
      into   l_min_delay
      from   wb_rt_platform_properties
      where  property_path = 'property.RuntimePlatform.0.purge_minimum_minutes'
         or  property_path = 'property.RuntimePatform.0.purge_minimum_minutes';

      if l_min_delay < 1
      then
        l_min_delay := 1;  -- set minimum value to 1 minute
      end if;
    exception
      when others then
        l_min_delay := 10;  -- set default value
    end;

    return l_min_delay;
  end;
/

CREATE OR REPLACE FUNCTION get_delay
    return number
  is
    l_delay number(10);
  begin
    l_delay := 24;  -- default value : any entry older than 24 hours may be purged

    begin
      select to_number(property_value)
      into   l_delay
      from   wb_rt_platform_properties
      where  property_path = 'property.RuntimePlatform.0.purge_delay_hours'
         or  property_path = 'property.RuntimePatform.0.purge_delay_hours';

      if l_delay < 1
      then
        l_delay := 1;  -- set minimum value to 1 hour
      end if;
    exception
      when others then
        l_delay := 24;  -- set default value
    end;

    l_delay := l_delay * 60;  -- convert hours to minutes
    return l_delay;
  end;
/

CREATE OR REPLACE PROCEDURE create_stage_table(
      p_exec_start IN VARCHAR,
      p_exec_end   IN VARCHAR,
      l_min_delay NUMBER,
      l_delay     NUMBER)
    AS
      statment VARCHAR2(4000);
    BEGIN
      statment := 'CREATE TABLE temp_executions AS SELECT audit_execution_id FROM wb_rt_audit_executions                
    WHERE (creation_date BETWEEN to_date(' || dbms_assert.enquote_literal(p_exec_start) || ') AND to_date(' || dbms_assert.enquote_literal(p_exec_end)||') )                
    AND parent_audit_execution_id IS NULL                
    AND (execution_operator_id    IS NULL                
    OR execution_operator_id NOT  IN                  
    (SELECT execution_operator_id                  
    FROM wb_rt_def_execution_operators                  
    WHERE upper(operator_name) LIKE ''%SCHEDULER''                  
    ))                
    AND (least(creation_date, sysdate - ( ' || l_min_delay/1440 ||  ')) = creation_date)                
    AND (audit_status                                         = wb_rt_constants.EXECUTION_STATUS_COMPLETE                
    OR audit_status                                           = wb_rt_constants.EXECUTION_STATUS_INACTIVE                
    OR least(creation_date, sysdate - ( ' || l_delay/1440 || '))   = creation_date)';
      dbms_output.put_line(statment); --debug purpose
      EXECUTE immediate STATMENT;
    END;
/

CREATE OR REPLACE PROCEDURE disable_constraints
AS
  state VARCHAR2(4000);
  --owner VARCHAR2(20);
BEGIN
  --owner := sys_context('USERENV', 'CURRENT_USER');
  FOR C IN
  (SELECT A.constraint_name owner_constraint,
    A.table_name owner_table
  FROM user_constraints A
  WHERE A.table_name LIKE 'OWB$WB_RT_%' --WB_RT_% for paris
  AND A.constraint_type='R'
  AND A.table_name!='OWB$WB_RT_AUDIT_EXECUTIONS' --keep the 2 r_constraints on wb_rt_audit_executions, WB_RT_AUDIT_EXECUTIONS for PARIS
  )
  LOOP
    state := 'ALTER TABLE ' || C.owner_table || ' disable novalidate constraint '|| C.owner_constraint;
    dbms_output.put_line(state);
    EXECUTE immediate state;
  END LOOP;
END;
/
    
CREATE OR REPLACE PROCEDURE enable_constraints
AS
  state VARCHAR2(4000);
  --owner VARCHAR2(20);
BEGIN
  --owner := sys_context('USERENV', 'CURRENT_USER');
  FOR C IN
  (SELECT a.constraint_name,
    a.table_name
  FROM user_constraints A
  WHERE A.table_name LIKE 'OWB$WB_RT_%'  --WB_RT_% for paris
  AND A.constraint_type='R'
  AND A.status='DISABLED'
  )
  LOOP
    state := 'ALTER TABLE '|| C.table_name || ' enable validate constraint ' || C.constraint_name;
    dbms_output.put_line(state);
    EXECUTE immediate state;
  END LOOP;
END;
/

CREATE OR REPLACE PROCEDURE del_executions(deleted_rows IN NUMBER)
AS
type dtArray IS TABLE OF NUMBER(22) INDEX BY binary_integer;
  v_mid_to_delete dtArray;
  v_execution_id dtArray;
  v_rta_iid dtArray;
  v_message_id dtArray;
  v_message_line_id dtArray;
  v_rte_iid dtArray;
  v_rtd_iid dtArray;
  l_start NUMBER;
BEGIN
  l_start     :=dbms_utility.get_time;
  SELECT audit_execution_id bulk collect INTO v_mid_to_delete FROM temp_executions WHERE rownum < deleted_rows+1;
  DBMS_OUTPUT.PUT_LINE('Used: ' || (dbms_utility.get_time-l_start) || 'ms to select');
  l_start     :=dbms_utility.get_time;
  -- don't disable the inline constraints on executions table
  forall i IN 1 .. v_mid_to_delete.COUNT
  DELETE FROM wb_rt_audit_executions WHERE audit_execution_id = v_mid_to_delete(i) returning audit_execution_id bulk collect   INTO v_execution_id;
  DBMS_OUTPUT.PUT_LINE('Used: ' || (dbms_utility.get_time-l_start) || 'ms to delete from wb_rt_audit_executions');
  l_start     :=dbms_utility.get_time;
  --level 1
    forall i IN 1 .. v_execution_id.count
    DELETE FROM wb_rt_audit_messages WHERE audit_execution_id = v_execution_id(i) returning audit_message_id bulk collect INTO v_message_id;
    DBMS_OUTPUT.PUT_LINE('Used: ' || (dbms_utility.get_time-l_start) || 'ms to delete from wb_rt_audit_messages');
    l_start     :=dbms_utility.get_time;
  
    forall i IN 1 .. v_execution_id.count 
    DELETE FROM wb_rt_audit WHERE rte_id = v_execution_id(i) returning rta_iid bulk collect INTO v_rta_iid;
    DBMS_OUTPUT.PUT_LINE('Used: ' || (dbms_utility.get_time-l_start) || 'ms to delete from wb_rt_audit');
    l_start     :=dbms_utility.get_time;
  
    forall i IN 1 .. v_execution_id.count
    DELETE FROM wb_rt_audit_files WHERE audit_execution_id = v_execution_id(i);
    DBMS_OUTPUT.PUT_LINE('Used: ' || (dbms_utility.get_time-l_start) || 'ms to delete from wb_rt_audit_files');
    l_start     :=dbms_utility.get_time;
  
    forall i IN 1 .. v_execution_id.count
    DELETE FROM wb_rt_audit_parameters WHERE audit_execution_id = v_execution_id(i);
    DBMS_OUTPUT.PUT_LINE('Used: ' || (dbms_utility.get_time-l_start) || 'ms to delete from wb_rt_audit_parameters');
    l_start     :=dbms_utility.get_time;
 --level 2
    forall i IN 1 .. v_rta_iid.count
    DELETE FROM wb_rt_operator WHERE rta_iid=v_rta_iid(i);
    DBMS_OUTPUT.PUT_LINE('Used: ' || (dbms_utility.get_time-l_start) || 'ms to delete from wb_rt_operator');
    l_start     :=dbms_utility.get_time;
    
    forall i IN 1 .. v_rta_iid.count
    DELETE FROM wb_rt_feedback WHERE rta_iid=v_rta_iid(i);
    DBMS_OUTPUT.PUT_LINE('Used: ' || (dbms_utility.get_time-l_start) || 'ms to delete from wb_rt_feedback');
    l_start     :=dbms_utility.get_time;
  
    forall i IN 1 .. v_rta_iid.count
    DELETE FROM wb_rt_error_sources WHERE rta_iid=v_rta_iid(i);
    DBMS_OUTPUT.PUT_LINE('Used: ' || (dbms_utility.get_time-l_start) || 'ms to delete from wb_rt_error_sources');
    l_start     :=dbms_utility.get_time;

    forall i IN 1 .. v_rta_iid.count
    DELETE FROM wb_rt_errors WHERE rta_iid=v_rta_iid(i) returning rte_iid bulk collect INTO v_rte_iid;
    DBMS_OUTPUT.PUT_LINE('Used: ' || (dbms_utility.get_time-l_start) || 'ms to delete from wb_rt_errors');
    l_start     :=dbms_utility.get_time;

    forall i IN 1 .. v_rta_iid.count
    DELETE FROM wb_rt_audit_detail WHERE rta_iid=v_rta_iid(i) returning rtd_iid bulk collect INTO v_rtd_iid;
    DBMS_OUTPUT.PUT_LINE('Used: ' || (dbms_utility.get_time-l_start) || 'ms to delete from wb_rt_audit_detail');
    l_start     :=dbms_utility.get_time;
  
    forall i IN 1 .. v_rta_iid.count
    DELETE FROM wb_rt_audit_amounts WHERE rta_iid=v_rta_iid(i);
    DBMS_OUTPUT.PUT_LINE('Used: ' || (dbms_utility.get_time-l_start) || 'ms to delete from wb_rt_audit_amounts');
    l_start     :=dbms_utility.get_time;
  --level 3
    forall i IN 1 .. v_rte_iid.count
    DELETE FROM wb_rt_error_rows WHERE rte_iid=v_rte_iid(i);
    DBMS_OUTPUT.PUT_LINE('Used: ' || (dbms_utility.get_time-l_start) || 'ms to delete from wb_rt_error_rows');
    l_start     :=dbms_utility.get_time;
    forall i IN 1 .. v_rtd_iid.count
    DELETE FROM wb_rt_audit_struct WHERE rtd_iid=v_rtd_iid(i);
    DBMS_OUTPUT.PUT_LINE('Used: ' || (dbms_utility.get_time-l_start) || 'ms to delete from wb_rt_audit_struct');
    l_start     :=dbms_utility.get_time;
  --level 2
    forall i IN 1 .. v_message_id.count
    DELETE FROM wb_rt_errors WHERE rtm_id=v_message_id(i) returning rte_iid bulk collect INTO v_rte_iid;
    DBMS_OUTPUT.PUT_LINE('Used: ' || (dbms_utility.get_time-l_start) || 'ms to delete from wb_rt_errors');
    l_start     :=dbms_utility.get_time;

    forall i IN 1 .. v_message_id.count
    DELETE FROM wb_rt_audit_message_lines WHERE audit_message_id=v_message_id(i) returning audit_message_line_id bulk collect INTO v_message_line_id;
    DBMS_OUTPUT.PUT_LINE('Used: ' || (dbms_utility.get_time-l_start) || 'ms to delete from wb_rt_audit_message_lines');
    l_start     :=dbms_utility.get_time;
  --level 3
    forall i IN 1 .. v_rte_iid.count
    DELETE FROM wb_rt_error_rows WHERE rte_iid=v_rte_iid(i);
    DBMS_OUTPUT.PUT_LINE('Used: ' || (dbms_utility.get_time-l_start) || 'ms to delete from wb_rt_error_rows');
    l_start     :=dbms_utility.get_time;
    forall i IN 1 .. v_message_line_id.count
    DELETE FROM wb_rt_audit_message_parameters WHERE audit_message_line_id=v_message_line_id(i);
    DBMS_OUTPUT.PUT_LINE('Used: ' || (dbms_utility.get_time-l_start) || 'ms to delete from wb_rt_audit_message_parameters');
    l_start     :=dbms_utility.get_time;

  forall i IN 1 .. v_mid_to_delete.COUNT
  DELETE FROM temp_executions WHERE audit_execution_id = v_mid_to_delete(i);
  
  DBMS_OUTPUT.PUT_LINE('Used: ' || (dbms_utility.get_time-l_start) || 'ms to delete');
  
 
  exception
    when others then
      rollback;
END;
/

CREATE OR REPLACE PROCEDURE del_all_executions(batch_count in NUMBER)
AS
  total_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO total_count FROM temp_executions;
  WHILE total_count>0
  LOOP
    del_executions(batch_count);
    COMMIT;
    total_count:=total_count-batch_count;
  END LOOP;
END;
/

DECLARE
  l_start VARCHAR2(40);
  l_end   VARCHAR2(40);
  l_delay number(10);
  l_min_delay number(10);
BEGIN
  l_start := '&1';
  l_end   := '&2';
  l_delay := get_delay();
  l_min_delay := get_min_delay();
  --1), create the stage table first
  BEGIN
    EXECUTE immedite 'drop table temp_executions';
  EXCEPTION
  WHEN OTHERS THEN
    NULL;
  END;
  create_stage_table(l_start,l_end,l_min_delay,l_delay);
  --2), disable the constraint
  disable_constraint;  
  --4), do the deletion
  del_all_executions(1000);
  --5), enable the constraint again
  enable_constraints;
END;
/

/*FOR c IN
  (SELECT DISTINCT LEVEL,
    REF_TABLE,
    REF_CONSTRAINT,
    ref_column,
    OWNER_TABLE,
    OWNER_CONSTRAINT,
    owner_column
  FROM
    (SELECT a.constraint_name owner_constraint,
      a.table_name owner_table,
      c.column_name owner_column,
      b.constraint_name ref_constraint,
      b.table_name ref_table,
      d.column_name ref_column
    FROM all_constraints A,
      all_constraints B,
      all_cons_columns C,
      all_cons_columns D
    WHERE A.r_constraint_name=B.constraint_name
    AND A.constraint_name    =C.constraint_name
    AND b.constraint_name    =d.constraint_name
    AND A.table_name LIKE 'OWB$WB_RT_%'
    AND A.TABLE_NAME!    =B.TABLE_NAME
    AND A.constraint_type='R'
    AND A.owner=owner
    )
    START WITH REF_TABLE        ='OWB$WB_RT_AUDIT_EXECUTIONS'
    CONNECT BY PRIOR OWNER_TABLE=REF_TABLE
  ORDER BY LEVEL)*/
  
  /*FOR c IN
  (SELECT DISTINCT LEVEL,
    REF_TABLE,
    REF_CONSTRAINT,
    ref_column,
    OWNER_TABLE,
    OWNER_CONSTRAINT,
    owner_column
  FROM
    (SELECT a.constraint_name owner_constraint,
      a.table_name owner_table,
      c.column_name owner_column,
      b.constraint_name ref_constraint,
      b.table_name ref_table,
      d.column_name ref_column
    FROM all_constraints A,
      all_constraints B,
      all_cons_columns C,
      all_cons_columns D
    WHERE A.r_constraint_name=B.constraint_name
    AND A.constraint_name    =C.constraint_name
    AND b.constraint_name    =d.constraint_name
    AND A.table_name LIKE 'WB_RT_%'
    AND A.TABLE_NAME!    =B.TABLE_NAME
    AND A.constraint_type='R'
    AND A.owner=owner
    )
    START WITH REF_TABLE        ='WB_RT_AUDIT_EXECUTIONS'
    CONNECT BY PRIOR OWNER_TABLE=REF_TABLE
  ORDER BY LEVEL
  ) -- paris*/