Shrink OWB runtime

We were experiencing some slowness (or terrible slowness, depends on when you asked the question) with retrieving data from the Repository Audit browser. It is a development environment so we were not overly interested in what we had been goofing about with mappings and process flows, deployment and execution. When enough waiting was enough, I looked for something that could help. And I found A VERY DANGEROUS solution here. I cannot stress enough that this is NOT something you will do in a production environment. At least not until you have tested it completely and made sure you have all the backups you have to make and then some extra backups of the things that cannot possibly go wrong.

But it proved useful for us, so why can’t it be useful for you?

I copied the necessary code below. It must be run as the design repository owner and the runtime service must be stopped. I presume that if you are prepared to try this, you know how to stop the runtime service. If not, please don’t use this script and do some reading in the manual first or look in google for the term “service_doctor.sql”.


==================================================================
REM sqlplus <RT_OWNER>/<RT_PASSWORD>@<RT_CONNECT>
REM @truncate_audit_execution_tables.sql
REM
REM to truncate wb_rt_audit_executions and dependent audit tables
REM in the runtime repository
REM
set echo off
set verify off
rem
rem 'truncate_audit_execution_tables : begin'
rem
alter table wb_rt_feedback disable constraint fk_rtfb_rta;
truncate table wb_rt_feedback;
rem 'wb_rt_feedback truncated'
rem
alter table wb_rt_error_sources disable constraint fk_rts_rta;
truncate table wb_rt_error_sources;
rem 'wb_rt_error_sources truncated'
rem
alter table wb_rt_error_rows disable constraint fk_rtr_rte;
truncate table wb_rt_error_rows;
rem 'wb_rt_error_rows truncated'
rem
alter table wb_rt_errors disable constraint fk_rter_rta;
alter table wb_rt_errors disable constraint fk_rter_rtm;
truncate table wb_rt_errors;
rem 'wb_rt_errors truncated'
rem
alter table wb_rt_audit_struct disable constraint fk_rtt_rtd;
truncate table wb_rt_audit_struct;
rem 'wb_rt_audit_struct truncated'
rem
alter table wb_rt_audit_detail disable constraint fk_rtd_rta;
truncate table wb_rt_audit_detail;
rem 'wb_rt_audit_detail truncated'
rem
alter table wb_rt_audit_amounts disable constraint fk_rtam_rta;
truncate table wb_rt_audit_amounts;
rem 'wb_rt_audit_amounts truncated'
rem
alter table wb_rt_operator disable constraint fk_rto_rta;
truncate table wb_rt_operator;
rem 'wb_rt_operator truncated'
rem
alter table wb_rt_audit disable constraint fk_rta_rte;
truncate table wb_rt_audit;
rem 'wb_rt_audit truncated'
rem
alter table wb_rt_audit_parameters disable constraint ap_fk_ae;
truncate table wb_rt_audit_parameters;
rem 'wb_rt_audit_parameters truncated'
rem
alter table wb_rt_audit_messages disable constraint am_fk_ae;
delete from wb_rt_audit_messages where audit_execution_id is not null;
rem 'wb_rt_audit_messages deleted'
rem 'wb_rt_audit_message_lines cascade deleted'
rem 'wb_rt_audit_message_parameters cascade deleted'
rem
alter table wb_rt_audit_files disable constraint af_fk_ae;
delete from wb_rt_audit_files where audit_execution_id is not null;
rem 'wb_rt_audit_files deleted'
rem
truncate table wb_rt_audit_executions;
rem 'wb_rt_audit_executions truncated'
rem
delete from wb_rt_notify_queue_tab where enq_time < sysdate -7;
rem
rem 'wb_rt_notify_queue_tab deleted older than 7 days'
rem
create table save_wb_rt_not_queue_tab as
select * from wb_rt_notify_queue_tab;
rem
truncate table wb_rt_notify_queue_tab;
rem
insert into wb_rt_notify_queue_tab
select * from save_wb_rt_not_queue_tab;
rem
commit;
rem
drop table save_wb_rt_not_queue_tab;
rem
rem 'Table wb_rt_notify_queue_tab saved, truncated and re-inserted'
rem
alter table wb_rt_feedback enable constraint fk_rtfb_rta;
alter table wb_rt_error_sources enable constraint fk_rts_rta;
alter table wb_rt_error_rows enable constraint fk_rtr_rte;
alter table wb_rt_errors enable constraint fk_rter_rta;
alter table wb_rt_errors enable constraint fk_rter_rtm;
alter table wb_rt_audit_struct enable constraint fk_rtt_rtd;
alter table wb_rt_audit_detail enable constraint fk_rtd_rta;
alter table wb_rt_audit_amounts enable constraint fk_rtam_rta;
alter table wb_rt_operator enable constraint fk_rto_rta;
alter table wb_rt_audit enable constraint fk_rta_rte;
alter table wb_rt_audit_parameters enable constraint ap_fk_ae;
alter table wb_rt_audit_messages enable constraint am_fk_ae;
alter table wb_rt_audit_files enable constraint af_fk_ae;
rem
rem 'truncate_audit_execution_tables : end'
rem
commit;
--