Sunday, September 14, 2014

Moving Audit table AUD$ to its own tablespace



   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;