The CLEANUP_AUDIT_DATA stored procedure can be used to cleanup audit data that is stored in the database. This procedure will delete data from the IN_AUDIT_DETAIL, and IN_AUDIT_OBJ, and IN_AUDIT tables based on the value of the KEEP_DAYS parameter or the BEGIN_DATETIME and END_DATETIME parameters.
It is advised to only execute this procedure after hours or on weekends to ensure minimal impact to the Perceptive Content application.
Transactions
From a transactional standpoint, the number of rows is based on qualifying rows in the IN_AUDIT table but the transaction also includes the deletion of rows from the IN_AUDIT_OBJ and IN_AUDIT_DETAIL tables which could have more or less than 100,000 each that correspond to the rows in the IN_AUDIT table. This means that each transaction could affect roughly three times the number of rows as specified in the BATCH_SIZE parameter. A BATCH_SIZE of 100,000 could result in a transaction size of roughly 300,000 or more rows.
If necessary, utilize smaller batch sizes, using the BATCH_SIZE parameter, to help keep transaction sizes smaller and reduce the scope and duration of database resources.
Parallelism
It is not possible to scope a transaction across multiple statements/tables when using parallelism due to the ORA-12839 error. Because of that limitation, transactions will be handled in the following manner based on the specified degree of parallelism.
If PARALLEL > 1
- Transactions will be scoped for each table and will commit/rollback after rows are deleted from each of the three tables.
- If an error occurs only the records for the current table is rolled back.
If PARALLEL = 1 (NOPARALLEL)
- Transactions will be scoped across all three tables (entire batch) and the commit/rollback of deleted rows will affect all three tables.
- If an error occurs then all records for the batch (all three tables) is rolled back
| Parameter | Description | |
|---|---|---|
| RUN_MODE | Type: | INPUT |
| Datatype: | VARCHAR2(8) | |
| Default Value: | HELP | |
| Description | Indicates the mode to use during execution. | |
| Options |
HELP = Display usage details. COUNT = Count qualifying rows. DELETE = Delete qualifying rows. TRUNCATE = Truncate audit tables. |
|
| KEEP_DAYS | Type: | INPUT |
| Datatype: | NUMBER | |
| Default Value: | NULL | |
| Description | Indicates the number of days to retain. | |
| Options | 0 = Delete All Rows Any number of days greater than 0. |
|
| STRAT_TIMESTAMP | Type: | INPUT |
| Datatype: | TIMESTAMP | |
| Default Value: | Null | |
| Description | Starting timestamp for rows to delete. Inclusive. | |
| Options | Any timestamp less than end timestamp. | |
| END_TIMESTAMP | Type | INPUT |
| Datatype: | TIMESTAMP | |
| Default Value: | NULL | |
| Description | Ending timestamp for rows to delete. Inclusive. | |
| Options | Any timestamp greater than start timestamp. | |
| CONVERT_TO_UTC | Type | INPUT |
| Datatype: | VARCHAR2(3) | |
| Default Value: | Yes | |
| Description | Whether to convert the input timestamps to UTC to align with the timestamps in the database tables which are stored as UTC. | |
| Options |
YES = Convert to UTC NO = Do Not Convert to UTC |
|
| USE_TIME | Type | INPUT |
| Datatype: | VARCHAR2(3) | |
| Default Value: | No | |
| Description | Whether to include the time part of timestamps when evaluating which records to delete. | |
| Options |
NO = Do not consider the time part of the timestamp. YES = Consider the time part for more granular timestamps. |
|
| MAX_MINUTES | Type | INPUT |
| Datatype: | NUMBER | |
| Default Value: | 0 | |
| Description | Maximum number of minutes to execute. This only applies if using BATCH_SIZE greater than 0. | |
| Options |
0 = No Time Limit Any number of minutes greater than 0. . |
|
| BATCH_SIZE | Type | INPUT |
| Datatype: | NUMBER | |
| Default Value: | 10,000 | |
| Description | Indicates the batch size if breaking up deletion into smaller transactions. Note that actual transaction sizes are roughly three times larger than the specified value for BATCH_SIZE since all three audit tables are included within a single transaction. | |
| Options |
0 = Single transaction 1 or more rows. . |
|
| INCLUDE_SUMMARY | Type | INPUT |
| Datatype: | VARCHAR2(3) | |
| Default Value: | Yes | |
| Description | Specifies whether to fetch and display the row counts of all qualifying rows for each table. Fetching summary data during execution could result in a much longer execution time as all qualifying rows are counted before any rows are deleted. | |
| Options | YES = Fetch and display the number of qualifying rows for each table NO = Do not fetch and display the number of qualifying rows. |
|
| PARALLEL | Type | INPUT |
| Datatype: | NUMBER | |
| Default Value: | 1 | |
| Description | 1 or more. 1 = NOPARALLEL |
|
| Options | YES = Fetch and display the number of qualifying rows for each table NO = Do not fetch and display the number of qualifying rows. |
|
| VERBOSE | Type | INPUT |
| Datatype: | VARCHAR2(3) | |
| Default Value: | No | |
| Description | Specifies whether to display additional details in the log, including SQL statements. | |
| Options |
NO = No additional logging YES = Include extra details in the log |
|
Exceptions
- FAILED_CHECK
- IS_EXECUTING
- CLEANUP_ERROR – An error occurred while deleting qualifying audit data.
- OTHERS
Recommended Index
The following index is recommended to facilitate efficient searches using the CREATION_TIME column and should be created prior to executing the CLEANUP_AUDIT_DATA stored procedure.
CREATE INDEX AUDIT_IDX_01 ON INUSER.IN_AUDIT ( CREATION_TIME ASC, AUDIT_ID ) TABLESPACE INDX;
Examples
- Display Usage Information Only
SET SERVEROUTPUT ON;
EXEC IN_DB_UTIL.CLEANUP_AUDIT_DATA;
EXEC IN_DB_UTIL.CLEANUP_AUDIT_DATA('HELP');
- KEEP_DAYS Example
The following will delete all audit data that is older than 365 days using batch sizes of 100,000 rows. Please see the information above regarding transaction sizes and parallelism for details on how the transactions are scoped during the execution of the CLEANUP_AUDIT_DATA procedure.
SET SERVEROUTPUT ON; BEGIN IN_DB_UTIL.CLEANUP_AUDIT_DATA ( RUN_MODE => 'DELETE' ,KEEP_DAYS => 365 ,MAX_MINUTES => 60 ,BATCH_SIZE => 100000 ,PARALLEL => 4 ); END; /
- Date Range Example
The following example will delete all audit data that was created on December 25, 2021 between 8am and 5pm using a single transaction (no batches).
- Note that to preserve the time part of the timestamps you must set USE_TIME = 1
- Note that CONVERT_TO_UTC = 1 (default) which means the provided timestamps will be adjusted to the UTC timestamp, based on the server differential, so that it aligns with the timestamps in the database.
SET SERVEROUTPUT ON; BEGIN IN_DB_UTIL.CLEANUP_AUDIT_DATA ( RUN_MODE => 'DELETE' ,START_TIMESTAMP => '2021-12-25 08:00' ,END_TIMESTAMP => '2021-12-25 17:00' ,CONVERT_TO_UTC => 'YES' ,USE_TIME => 'YES' ,BATCH_SIZE => 0 ,VERBOSE => 'YES' ); END; /
Truncate Table Example
The following example will truncate each of the three audit tables. This is the fastest way to remove all rows from each of the tables. This operation is unrecoverable.
SET SERVEROUTPUT ON; EXEC IN_DB_UTIL.CLEANUP_AUDIT_DATA(RUN_MODE=>'TRUNCATE',INCLUDE_SUMMARY=>'NO');