Truncate Audit Trail Table AUD$

###Take the export###

exp aud_backup/audit file=/local/oracle/UATWCS7/aud_backup.dmp log=aud_backup.log tables=SYS.AUD$ buffer=5000000

log==>
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Export done in US7ASCII character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Current user changed to SYS
. . exporting table                           AUD$  929290860 rows exported
EXP-00091: Exporting questionable statistics.

##
NAME                          GBYTES       USED       FREE   PCT_USED    LARGEST   MAX_SIZE PCT_MAX_USED EXTENT_MAN SEGMEN
------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ------------ ---------- ------
SYSTEM                        161.82     159.12        2.7      98.33       1536        157       101.35 LOCAL      MANUAL

##

col owner for a6
col segment_name for a50
select * from
(select owner,segment_name||'~'||partition_name segment_name,bytes/(1024*1024) size_m
from dba_segments
where tablespace_name = 'SYSTEM' ORDER BY BLOCKS desc) where rownum < 11;

OWNER  SEGMENT_NAME                                           SIZE_M
------ -------------------------------------------------- ----------
SYS    AUD$~                                                  160929  ==========>>>>>THIS
SYS    FGA_LOG$~                                                 984
SYS    IDL_UB1$~                                                 256
SYS    SYS_LOB0000000394C00013$$~                                 96
SYS    SOURCE$~                                                   80
SYS    SYS_LOB0000403209C00045$$~                                 71
SYS    C_OBJ#_INTCOL#~                                            37
SYS    IDL_UB2$~                                                  32
SYS    C_TOID_VERSION#~                                           24
SYS    SYS_LOB0000001067C00003$$~                                 20

###script####

#!/bin/bash
export ORACLE_SID=ABC
export ORACLE_HOME=/app/oracle/product/11.2.0/db
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/lib:$PATH
sqlplus "/as sysdba"<<-EOF
set echo on;
set serveroutput on;
set lines 200 pages 200
set time on;
set timing on;
set feedback on
spool TRUNCATE_SYS_AUD.log
Show user;
show parameter db_name
show parameter instance_number
SELECT NAME "DB_NAME", LOG_MODE, OPEN_MODE, RESETLOGS_TIME  FROM V\$DATABASE;
alter session enable parallel ddl;
alter session force parallel ddl parallel 20;
alter session set db_file_multiblock_read_count = 256;
alter session set "_px_max_granules_per_slave"=3000;
alter session set "_px_index_sampling"=3000;
alter session set "_px_dynamic_sample_size"=3000;
ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600;
alter session set parallel_force_local=TRUE;
truncate table aud$;
spool off
EOF
exit

##LOG===>

18:48:51 SQL> alter session enable parallel ddl;

Session altered.

Elapsed: 00:00:00.00
18:48:51 SQL> alter session force parallel ddl parallel 20;

Session altered.

Elapsed: 00:00:00.00
18:48:51 SQL> alter session set db_file_multiblock_read_count = 256;

Session altered.

Elapsed: 00:00:00.01
18:48:51 SQL> alter session set "_px_max_granules_per_slave"=3000;

Session altered.

Elapsed: 00:00:00.00
18:48:51 SQL> alter session set "_px_index_sampling"=3000;

Session altered.

Elapsed: 00:00:00.00
18:48:51 SQL> alter session set "_px_dynamic_sample_size"=3000;

Session altered.

Elapsed: 00:00:00.00
18:48:51 SQL> ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600;

Session altered.

Elapsed: 00:00:00.01
18:48:51 SQL> alter session set parallel_force_local=TRUE;

Session altered.

Elapsed: 00:00:00.00
18:48:51 SQL> truncate table aud$;

Table truncated.

Elapsed: 00:00:02.55
18:48:54 SQL> spool off

###
col owner for a6
col segment_name for a50
select * from
(select owner,segment_name||'~'||partition_name segment_name,bytes/(1024*1024) size_m
from dba_segments
where tablespace_name = 'SYSTEM' ORDER BY BLOCKS desc) where rownum < 11;


OWNER  SEGMENT_NAME                                           SIZE_M
------ -------------------------------------------------- ----------
SYS    FGA_LOG$~                                                 984
SYS    IDL_UB1$~                                                 256
SYS    SYS_LOB0000000394C00013$$~                                 96
SYS    SOURCE$~                                                   80
SYS    SYS_LOB0000403209C00045$$~                                 71
SYS    C_OBJ#_INTCOL#~                                            37
SYS    IDL_UB2$~                                                  32
SYS    C_TOID_VERSION#~                                           24
SYS    C_OBJ#~                                                    20
SYS    SYS_LOB0000001067C00003$$~                                 20

10 rows selected.

#

NAME                          GBYTES       USED       FREE   PCT_USED    LARGEST   MAX_SIZE PCT_MAX_USED EXTENT_MAN SEGMEN

------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ------------ ---------- ------

SYSTEM                        161.82       1.96     159.86       1.21       3968        157         1.25 LOCAL      MANUAL

#

How to Truncate, Delete, or Purge Rows from the Audit Trail Table AUD$ (Doc ID 73408.1)

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=240046292784578&id=73408.1&_afrWindowMode=0&_adf.ctrl-state=12uh2plej6_4