title: Truncating your orasso audit logs, with style link: https://onemoretech.wordpress.com/2011/08/18/truncating-your-orasso-audit-logs-with-style/ author: lembobro description: post_id: 850 created: 2011/08/18 16:28:47 created_gmt: 2011/08/18 20:28:47 comment_status: closed post_name: truncating-your-orasso-audit-logs-with-style status: publish post_type: post

Truncating your orasso audit logs, with style

This one comes courtesy of my friend Scott Z. It is a way to more surgically trim the OSSO (Oracle Application Server 10g SSO) audit log than the "say goodbye to everything" PL/SQL script the vendor provides. Whatever you think of Oracle's decision to store the audit log for Oracle Application Server 10g SSO (ORASSO) in the database, it doesn't reflect well on them that they neglected to build in any kind of automatic garbage collection. As a result that audit log table can grow really stupendously huge over time. Oracle does provide a script, $ORACLE_HOME/sso/admin/plsql/sso/purgelog.sql, but it doesn't take any arguments and will basically wipe out everything in the log if run. But, as usual, there is another, undocumented by Oracle, way. Do this in an sqlplus (or scripted) session against the infrastructure database as the ORASSO schema user: `

SQL> select max(log_date) from ORASSO.WWSSO_AUDIT_LOG_TABLE_T;

MAX(LOG_D
---------
18-AUG-11

SQL> select min(log_date) from ORASSO.WWSSO_AUDIT_LOG_TABLE_T;

MIN(LOG_D
---------
21-AUG-09

SQL> select count(1) from ORASSO.WWSSO_AUDIT_LOG_TABLE_T;

  COUNT(1)
----------
     14240

SQL> exec wwsso_audit_log.cleanup_audit_log(sysdate-60);

PL/SQL procedure successfully completed.

SQL> select min(log_date) from ORASSO.WWSSO_AUDIT_LOG_TABLE_T;

MIN(LOG_D
---------
19-JUN-11

SQL> select count(1) from ORASSO.WWSSO_AUDIT_LOG_TABLE_T;

  COUNT(1)
----------
      1716

SQL>

The first two commands tell you the latest and earliest dates of entries in the audit log. The middle one:

 exec wwsso_audit_log.cleanup_audit_log(sysdate-60);

` executes a stored procedure that truncates the log at 60 days before SYSDATE (today). The final two commands just confirm the change that has been wrought.

Copyright 2004-2019 Phil Lembo