Truncate SYS.AUD$ Using dbms_audit_mgmt [message #685072] |
Sun, 17 October 2021 19:21 |
|
mushy_1983
Messages: 5 Registered: October 2021
|
Junior Member |
|
|
Dear All,
I need to delete data older than 12 months SYS.AUD$ using DBMS_AUDIT_MGMT. I am not familiar much with DBMS_AUDIT_MGMT any help how can I delete data older than 12 months.
declare
retention_days number;
begin
retention_days := 100;
DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => SYSTIMESTAMP-5);
SYS.DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_DB_STD,use_last_arch_timestamp => TRUE);
end;
[Error] Execution (1: 1): ORA-46258: Cleanup not initialized for the audit trail
ORA-06512: at "AUDSYS.DBMS_AUDIT_MGMT", line 204
ORA-06512: at "AUDSYS.DBMS_AUDIT_MGMT", line 1085
ORA-06512: at line 5
Thanks
|
|
|
|
|
|
Re: Truncate SYS.AUD$ Using dbms_audit_mgmt [message #685080 is a reply to message #685076] |
Mon, 18 October 2021 05:37 |
|
mushy_1983
Messages: 5 Registered: October 2021
|
Junior Member |
|
|
I called up the init procedure but it takes too much time and still when I see its not on.
BEGIN
IF NOT SYS.DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED
(SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD)
THEN
SYS.dbms_output.put_line('Calling DBMS_AUDIT_MGMT.INIT_CLEANUP');
SYS.DBMS_AUDIT_MGMT.INIT_CLEANUP(
audit_trail_type => SYS.dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD,
default_cleanup_interval => 12);
else
dbms_output.put_line('Cleanup for STD was already initialized');
end if;
end;
--moderator edit: added [code] tags
[Updated on: Mon, 18 October 2021 06:15] by Moderator Report message to a moderator
|
|
|
|
Re: Truncate SYS.AUD$ Using dbms_audit_mgmt [message #685082 is a reply to message #685080] |
Mon, 18 October 2021 06:16 |
John Watson
Messages: 8960 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Quote:I called up the init procedure but it takes too much time How much time is "too much"? IF you have zillions of audit records and your AUD$ table is in your SYSTEM tablespace, it will take a long time as it moves the table to SYSAUX. You should probably move the audit trail before running the INIT_CLEANUP.
ps - I've added [code] tags to your previous post, please do so yourself in future.
|
|
|
|
|
|
|
|