Hello!
This question is related Oracle 11.2 and 12.x versions where we are supposed to use DBMS_AUDIT_MGMT package.
Question is related to Standard Audit Trail with conventional auditing (AUD$ and not unified auditing)
So imagine your AUD$ table is in SYSTEM tablespace. It is 200GB with data 10 years old.
This is the current situation and setting
-- CURRENT tablespace of AUD$
select tablespace_name as current_aud$_ts
from dba_segments
where (owner = 'SYS' and segment_name = 'AUD$');
CURRENT_AUD$_TS
------------------------------
SYSTEM
-- CURRENT destination
column current_aud$_destination_ts format a30
select parameter_value as current_aud$_destination_ts
from DBA_AUDIT_MGMT_CONFIG_PARAMS
where parameter_name = 'DB AUDIT TABLESPACE'
and audit_trail = 'STANDARD AUDIT TRAIL';
CURRENT_AUD$_DESTINATION_TS
------------------------------
SYSAUX
What is my goal?
I would like to cleanup data older than 1 year. I would like to delete old records.
After the cleanup I would possibly move data to SYSAUX.
The point is that it makes no sense to move such huge amount of data and then delete 90% of it. I would like to move only the needed 10% to SYSAUX.
I would NOT like to move the whole data to SYSAUX.
What is the problem?
INIT_CLEANUP moves the data to the default SYSAUX tablespace (or somewhere else if specified with SET_AUDIT_TRAIL_LOCATION parameter)
https://docs.oracle.com/database/121/ARPLS/d_audit_mgmt.htm#ARPLS65423
SET_AUDIT_TRAIL_LOCATON procedure also moves the data and does not only set a parameter. (Why does it not only set the location???)
https://docs.oracle.com/database/121/ARPLS/d_audit_mgmt.htm#ARPLS65427
My idea was to SET_AUDIT_TRAIL_LOCATION to SYSTEM tablespace like this
begin
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
'SYSTEM') ;
end;
/
But it had no effect. No errors. No nothing.... It is still SYSAUX tablespace.
column current_aud$_destination_ts format a30
select parameter_value as current_aud$_destination_ts
from DBA_AUDIT_MGMT_CONFIG_PARAMS
where parameter_name = 'DB AUDIT TABLESPACE'
and audit_trail = 'STANDARD AUDIT TRAIL';
CURRENT_AUD$_DESTINATION_TS
------------------------------
SYSAUX
Question
The question is how one can avoid moving the huge amount of data and start the cleanup while still using SYSTEM tablespace.
Shall I just delete from AUD$ without the DBMS_AUDIT_MGMT package? Is plain delete still supported?
Thanks,
RobK