Sign in or 
Location: Oracle Warehouse Builder
Discussion: Purge audit records in OWB at ease
Category:
Discussion Forum /
General Discussion
Share this
Showing 4 posts
htu |
Purge audit records in OWB at ease
Jul 30 2009, 9:09 AM EDT
We have fount out that the accumulated audit records in the OWB could impact the performance, especially taking long time for users starting the control center. Oracle provide a script template purge_audit_template.sql under %OWB_HOME%\owb\rtp\sql for you to purge audit records: @purge_audit_template.sql rt_owner {DEPLOYMENT | EXECUTION} {sql_predicate | ALL | DATE_RANGE} [start_date end_date] where rt_owner := e.g. MY_RUNTIME - Name of the Runtime Repository Owner audit_type :- DEPLOYMENT - Deployment Audit Data | EXECUTION - Execution Audit Data sql_predicate :- e.g. "number_script_run_errors > 0" - Selection Criteria e.g. deployment errors | ALL - Purge all deployment or execution audit data | DATE_RANGE - Purge audit data within given Date Range start_date :- e.g. TO_DATE('01-Jan-2003') - Start of Date Range end_date :- e.g. SYSDATE - End of Date Range You have to run it through SQLPlus and need to specify date range if you do not want to purge all the records. How about if you just want to keep 120 days of audit records and want to run it every night? I have developed a stored procedure owb_purge_audit based on the template, which will allow you just to do that. See the attachment for the code. Thanks, Hanming Do you find this valuable?
Keyword tags:
OWB
purge audit record
stored procedure
|
|
htu |
1. RE: Purge audit records in OWB at ease
Jul 30 2009, 9:16 AM EDT
Only 40 attachments allowed for this website? I can not load attachment. Bummer!
Do you find this valuable?
|
|
htu |
2. RE: Purge audit records in OWB at ease
Jul 30 2009, 9:31 AM EDT
Here is the header of the stored procedure:CREATE OR REPLACE PROCEDURE owb_purge_audit ( p_rep_owner VARCHAR2 DEFAULT USER -- rep owner , p_audit_type VARCHAR2 DEFAULT 'execution' , p_sql_predicate VARCHAR2 DEFAULT 'date_range' , p_start_dt DATE DEFAULT NULL , p_end_dt DATE DEFAULT NULL , p_remove_days NUMBER DEFAULT NULL -- default 7 days , p_keep_days NUMBER DEFAULT NULL -- default 120 days , p_act NUMBER DEFAULT 0 -- action:0-test;1-exec , p_lvl NUMBER DEFAULT 0 ) This is what you can do with it: 1. You can run it in test mode (p_act=>0), then it will just report back how many records will be purged 2. You can specify audit type to be purged: deployment or execution 3. You can safe guard your audit records by specifying keep days, e.g., p_keep_days=>90 will keep the most recent 90 days of audit records 4. You can select audit records to be purged based on date range (p_sql_predicate) just as in the original script template 5. You can select audit records to be purged based on how many day worth of the oldest audit records, e.g., p_remove_days=>7 will remove the oldest 7 days of audit records if they are beyond p_keep_days range. 6. It is a stored procedure, you can schedule it as database job. Since I can not upload it as attachment, I will send it to anyone who is interested in directly. Hanming Do you find this valuable? |
|
htu |
3. RE: Purge audit records in OWB at ease
Aug 13 2009, 9:54 AM EDT
OK, I was able to post the whole stored procedure in this blog: http://geotiger.wordpress.com/2009/08/13/purging-owb-audit-records-is-easy/ Do you find this valuable? |
