select owner, tablespace_name from dba_tables where table_name='AUD$';
CREATE SMALLFILE TABLESPACE SCOTT_AUDIT
DATAFILE SIZE 100M
AUTOEXTEND ON
NEXT 100K
MAXSIZE UNLIMITED
LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
SEGMENT SPACE MANAGEMENT AUTO DEFAULT COMPRESS FOR OLTP;
create table audx tablespace SCOTT_AUDIT
storage (initial 50k next 50k pctincrease 0)
as select * from aud$ where 1 = 2 ;
rename AUD$ to AUD$_drop ;
rename audx to aud$ ;
create index i_aud1
on aud$(sessionid, ses$tid)
tablespace SCOTT_AUDIT storage(initial 50k next 50k pctincrease 0) ;
drop table AUD$_drop ;
purge dba_recyclebin;
select owner, tablespace_name from dba_tables where table_name='AUD$';
set audit_trail=NONE in init.ora and create spfile
-- production only
sqlplus / as sysdba
audit dba;
select bytes from dba_segments where segment_name='AUD$'
select bytes from dba_segments where segment_name='AUD$_drop';
select count(1) from aud$;
These views will show you what auditing is configured:
Select * from dba_priv_audit_opts;
Select * from dba_stmt_audit_opts;
Select * from dba_obj_audit_opts;