aud$表空间迁移

system表空间的使用情况到了99%，查看占用最多空间的对象是aud$,使用DBMS_AUDIT_MGMT迁移到其他表空间

---

1.查看表空间的使用情况

sys@GHSJDB> select　　  
  2    b.tablespace_name "表空间",  
  3  　b.bytes/1024/1024 "大小M",  
  4   (b.bytes-sum(nvl(a.bytes,0)))/1024/1024 "已使用M",  
  5  　substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) "利用率"  
  6  　from dba_free_space a,dba_data_files b  
  7  　　where a.file_id=b.file_id  
  8      and b.tablespace_name='SYSTEM'  
  9  　　group by b.tablespace_name,b.file_name,b.bytes  
 10  　　order by b.tablespace_name; 

表空间                              大小M    已使用M 利用率
------------------------------ ---------- ---------- ----------
SYSTEM                               1830  1822.8125 99.60

2.查看最耗空间的对象

sys@GHSJDB> SELECT *  
  2     FROM (SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MB  
  3             FROM DBA_SEGMENTS  
  4             WHERE TABLESPACE_NAME = 'SYSTEM'  
  5            GROUP BY SEGMENT_NAME  
  6            ORDER BY 2 DESC)  
  7    WHERE ROWNUM < 10; 

SEGMENT_NAME                      MB
------------------------- ----------
AUD$                             936
IDL_UB1$                         248
SOURCE$                           72
C_OBJ#_INTCOL#                    60
IDL_UB2$                          34
C_TOID_VERSION#                   24
C_OBJ#                            21
SYS_LOB0000001067C00003$$         20

3.

sys@GHSJDB> truncate table aud$;

Table truncated.

4.创建迁移到的目标表空间

sys@GHSJDB> create tablespace records 
  2  datafile '/home/u01/app/oracle/oradata/ghsjdb/records01.dbf' size 2g autoextend on;

Tablespace created.

5.迁移到目标表空间

sys@GHSJDB>  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 => 'RECORDS');  
  5   END;  
  6   /  

PL/SQL procedure successfully completed.

6.

sys@GHSJDB> 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$                           RECORDS

至此sys.aud$表空间迁移完成。

–本文乃原创文章，请勿转载。如须转载请详细标明转载出处