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$表空间迁移完成。
–本文乃原创文章,请勿转载。如须转载请详细标明转载出处