OraFAQ Forum: Server Administration » Truncate SYS.AUD$ Using dbms_audit

Home » RDBMS Server » Server Administration » Truncate SYS.AUD$ Using dbms_audit_mgmt (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: Truncate SYS.AUD$ Using dbms_audit_mgmt [message #685076 is a reply to message #685072]

Mon, 18 October 2021 00:41

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Code: [Select all] [Show/ hide]

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Make sure that lines of code do not exceed 100 characters when you format.

ORA-46258: Cleanup not initialized for the audit trail
 *Cause: DBMS_AUDIT_MGMT.INIT_CLEANUP was not called for the erroring audit trail.
 *Action: Invoke the procedure, DBMS_AUDIT_MGMT.INIT_CLEANUP.

You must first call the INIT_CLEANUP procedure.

[Updated on: Mon, 18 October 2021 00:42]

Report message to a moderator

Re: Truncate SYS.AUD$ Using dbms_audit_mgmt [message #685077 is a reply to message #685072]

Mon, 18 October 2021 01:09

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

It is a good thing that your code didn't work Smile

Because your statement here Quote:

I need to delete data older than 12 months

is not met by your code here

Code: [Select all] [Show/ hide]

last_archive_time => SYSTIMESTAMP-5);

Report message to a moderator

Re: Truncate SYS.AUD$ Using dbms_audit_mgmt [message #685079 is a reply to message #685077]

Mon, 18 October 2021 05:35

mushy_1983
Messages: 5
Registered: October 2021

Junior Member

I was just testing that I can not change can you tell me the solution how I can make it work.

Report message to a moderator

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.

Code: [Select all] [Show/ hide]

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 #685081 is a reply to message #685080]

Mon, 18 October 2021 06:03

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Maybe you see but us we see nothing.
Please follow the guide you have been pointed to.

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: 8976
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.

Report message to a moderator

Re: Truncate SYS.AUD$ Using dbms_audit_mgmt [message #685083 is a reply to message #685082]

Mon, 18 October 2021 07:55

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

John Watson wrote on Mon, 18 October 2021 13:16

...
ps - I've added [code] tags to your previous post, please do so yourself in future.

... and indent the code.

Report message to a moderator

Re: Truncate SYS.AUD$ Using dbms_audit_mgmt [message #685241 is a reply to message #685083]

Fri, 19 November 2021 08:05

mushy_1983
Messages: 5
Registered: October 2021

Junior Member

Hi Everyone,

I have moved the table out of system tablespace and I can see that DBMS_AUDIT_MGMT.INIT_CLEANUP is initialized but still init_cleanup is not working. I am able to execute procedure successfully but nothing has been deleted.

Any suggestions I have the all the rights on SYS.AUD$ table.

Thanks

Report message to a moderator

Re: Truncate SYS.AUD$ Using dbms_audit_mgmt [message #685242 is a reply to message #685241]

Fri, 19 November 2021 11:51

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

Come on, man! Haven't you read the docs? After initializing the clean up, you actually have to do it: dbms_audit_mgmt.set_last_archive_timestamp and dbms_audit_mgmt.clean_audit_trail

Report message to a moderator

Re: Truncate SYS.AUD$ Using dbms_audit_mgmt [message #685248 is a reply to message #685242]

Tue, 23 November 2021 12:33

mushy_1983
Messages: 5
Registered: October 2021

Junior Member

Hi thanks for the correction yes it is working but I am not sure how its working. For testing I have set the timestamp for sysdate-1 so ideally it should delete the data only for one day but it keeps on running and running and deleting whole data. Can you please check and confirm the way how it works or any correction required at my side.

"exec DBMS_AUDIT_MGMT.set_last_archive_timestamp(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,last_archive_time =>SYSDATE-1);

EXEC DBMS_AUDIT_MGMT.clean_audit_trail(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,use_last_arch_timestamp => TRUE);

SELECT count(*) FROM SYS.AUD$
where NTIMESTAMP#>SYSDATE-1"

Report message to a moderator

Re: Truncate SYS.AUD$ Using dbms_audit_mgmt [message #685252 is a reply to message #685248]

Wed, 24 November 2021 03:47

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

Quote:

I have set the timestamp for sysdate-1 so ideally it should delete the data only for one day

You want to remove data for one day? Why?? There is no way to do that. Your setting will remove everything more than one day old.

[Updated on: Wed, 24 November 2021 03:47]

Report message to a moderator

Previous Topic:	log_archive_dest truncate
Next Topic:	ORA-30928: Connect by filtering phase runs out of temp tablespace

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 04 00:05:24 CDT 2025