清理AUD$审计表且迁移到普通表空间

jiawang

C:\Users\Administrator>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期日 9月 27 19:22:43 2020
Copyright (c) 1982, 2010, Oracle.  All rights reserved.


连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select segment_name, bytes/1024/1024/1024 as SIZE_GB from dba_segments where segment_name = 'AUD$';

SEGMENT_NAME
--------------------------------------------------------------------------------
   SIZE_GB
----------
AUD$
    13.5625


SQL> create tablespace tsp_audit datafile 'E:\oradata\orcl\tsp_audit.dbf' size 5g;
表空间已创建。

SQL> truncate table aud$;
表被截断。


SQL> select segment_name, bytes/1024/1024/1024 as SIZE_GB from dba_segments where segment_name = 'AUD$';

SEGMENT_NAME
--------------------------------------------------------------------------------
   SIZE_GB
----------
AUD$
.000061035



SQL> BEGIN
  2    DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
  3    AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  4    AUDIT_TRAIL_LOCATION_VALUE => 'tsp_audit');
  5    END;
  6     /

PL/SQL 过程已成功完成。

SQL> SELECT OWNER, TABLE_NAME, TABLESPACE_NAME
  2     FROM DBA_TABLES
  3     WHERE TABLE_NAME = 'AUD$'
  4    AND OWNER = 'SYS';

OWNER TABLE_NAME                     TABLESPACE_NAME
----- ------------------------------ ------------------------------
SYS   AUD$                           TSP_AUDIT

SQL>