♣
题目部分
【DB笔试面试829】在Oracle中,如何迁移或清理审计表SYS.AUD$?
♣
答案部分
在日常的数据库维护中,经常出现SYSTEM表空间被撑满,在绝大多数情况下是因为数据库登录审计的功能被启动了,此时一般建议把SYS.AUD$相关对象迁移到其它表空间,从而避免SYSTEM被用完的风险。
在Oracle 11g之前迁移方法如下所示:
ALTER TABLE SYS.AUDIT$ MOVE TABLESPACE USERS;
ALTER TABLE SYS.AUDIT_ACTIONS MOVE TABLESPACE USERS;
ALTER TABLE SYS.AUD$ MOVE TABLESPACE USERS;
ALTER TABLE SYS.AUD$ MOVE LOB(SQLBIND) STORE AS SYS_IL0000000384C00041$$ (TABLESPACE USERS);
ALTER TABLE SYS.AUD$ MOVE LOB(SQLTEXT) STORE AS SYS_IL0000000384C00041$$ (TABLESPACE USERS);
ALTER INDEX SYS.I_AUDIT REBUILD ONLINE TABLESPACE USERS;
ALTER INDEX SYS.I_AUDIT_ACTIONS REBUILD ONLINE TABLESPACE USERS;
从Oracle 11g开始可以使用DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION进行迁移:
EXEC DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(AUDIT_TRAIL_TYPE=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,AUDIT_TRAIL_LOCATION_VALUE => 'USERS');
如下示例为授予审计的一些权限:
GRANT AUDIT ANY TO LHR_TEST;
GRANT AUDIT SYSTEM TO LHR_TEST;
在Oracle 11g之前通过手工清理的方式或自定义作业来定期清理SYS.AUD$表,如下:
TRUNCATE TABLE SYS.AUD$;
DELETE FROM SYS.AUD$ WHERE OBJ$NAME='EMP';
需要注意的是,如果AUD表过大,那么直接TRUNCATE AUD表,系统要立即释放大量的EXTENTS,会严重影响系统性能。可以通过如下2个步骤逐步释放EXTENTS:
① 清空数据并且保留原来的EXTENTS:
TRUNCATE TABLE SYS.AUD$ REUSE STORAGE;
在这里,REUSE STORAGE是TRUNCATE的一个参数,表示保持原来的存储不变。一般情况下,SQL命令“TRUNCATE TABLE TABLE_NAME;”其实就是“TRUNCATE TABLE TABLE_NAME DROP STORAGE;”。DROP STORAGE是TRUNCATE TABLE的默认参数。
② 逐步回缩EXTENTS:
ALTER TABLE SYS.AUD$ DEALLOCATE UNUSED KEEP 5000M;
ALTER TABLE SYS.AUD$ DEALLOCATE UNUSED KEEP 2000M;
……
ALTER TABLE SYS.AUD$ DEALLOCATE UNUSED KEEP 10M;
需要注意的是,在执行的时候,可以根据实际情况调整每次回缩空间的大小。
若审计在OS和XML选项下进行手动删除审计文件。在Oracle 11g中通过DBMS_AUDIT_MGMT包下的子过程进行手动或定期清理。下面的过程可以迁移审计记录到USERS表空间:
conn / as sysdba
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
AUDIT_TRAIL_LOCATION_VALUE => 'USERS');
END;
/
使用包DBMS_AUDIT_MGMT下的INIT_CLEANUP过程可以设置审计的清除间隔,还有很多其它的实用存储过程请参考官方文档。
本文选自《Oracle程序员面试笔试宝典》,作者:小麦苗