Edit D:\app\Administrator\product\11.2.0\dbhome_1\owb\rtp\jrtaudit\owbsys\purge_audit

Edit D:\app\Administrator\product\11.2.0\dbhome_1\owb\rtp\jrtaudit\owbsys\purge_audit_tables.sql

Rem
Rem $Header: purge_audit_tables.sql 21-jan-2008.02:52:30 tzrose   Exp $
Rem
Rem purge_audit_tables.sql
Rem
Rem Copyright (c) 2008, Oracle.  All rights reserved.  
Rem
Rem    NAME
Rem      purge_audit_tables.sql - Purge Runtime Audit Tables
Rem
Rem    USAGE
Rem      purge_mode  :- ALL                          - Purge all audit data
Rem                  |  DATE_RANGE                   - Purge audit data within given Date Range
Rem
Rem      start_date  :- e.g. TO_DATE('01-Jan-2008')  - Start of Date Range
Rem      end_date    :- e.g. SYSDATE                 - End of Date Range
Rem
Rem    DESCRIPTION
Rem      This script is used to purge execution audit data from the runtime audit tables
Rem      for a heterogeneous environment using OWBSYS in an Oracle database.
Rem
Rem    NOTES
Rem      This SQL*Plus script can be called from the SQL*Plus shell.
Rem      The start_date and end_date parameters, if supplied, must conform to the SQL syntax rules for "date".
Rem
Rem    EXAMPLES
Rem      sqlplus user/password@tns_name @purge_audit_tables.sql ALL null null
Rem      sqlplus user/password@tns_name @purge_audit_tables.sql DATE_RANGE TO_DATE('01-Jan-2008') SYSDATE
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    tzrose      01/21/08 - Created
Rem

SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100

set serveroutput on
set verify off

whenever sqlerror exit failure;

set role OWB_USER;

declare
  l_mode           varchar2(10);       -- ALL or DATE_RANGE
  l_start          date;               -- Start of date range
  l_end            date;               -- End of date range

begin
  l_mode := '&1';
  l_start := &2;
  l_end := &3;

if UPPER(l_mode) = 'ALL'
  then
    delete from wb_rt_ht_audit_job;
      
    dbms_output.put_line('ALL EXECUTION AUDIT DETAILS PURGED');
  end if;  
        
  if UPPER(l_mode) = 'DATE_RANGE'
  then
    delete from wb_rt_ht_audit_job where creation_date > l_start and creation_date < l_end;

dbms_output.put_line('ALL EXECUTION AUDIT DETAILS PURGED BETWEEN ' ||
                         to_char(l_start) || ' AND ' || to_char(l_end));
  end if;

commit;

end;

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de