Monday, September 12, 2011

Purging OSB Report data

Deleting report data generated by Oracle Service Bus (OSB) can be done via the Service Bus console.

Make sure you have deployed the " Message Reporting Purger " application. By default the application is set to new, so it is not active. Inititially start this application for requests.

Purging can done be done via the Console.

If you want to purge the data directly in the database, execute the following SQL.

delete from wli_qs_report_data rd
  where exists
    select 'x' 
    from wli_qs_report_attribute ra
    where ra.msg_guid = rd.msg_guid
    and ra.db_timestamp < trunc(sysdate) - 7
  delete from wli_qs_report_attribute ra
  where ra.db_timestamp < trunc(sysdate) - 7;

  alter table wli_qs_report_data deallocate unused;
  alter table wli_qs_report_data enable row movement;
  alter table wli_qs_report_data shrink space compact;

  alter table wli_qs_report_attribute deallocate unused;
  alter table wli_qs_report_attribute enable row movement;
  alter table wli_qs_report_attribute shrink space compact;

Post a Comment