在Oracle中，如何迁移或清理审计表SYS.AUD$？

DB宝 2021-03-23

7343

♣题目部分【DB笔试面试829】在Oracle中，如何迁移或清理审计表SYS.AUD$？

     




♣答案部分


在日常的数据库维护中，经常出现SYSTEM表空间被撑满，在绝大多数情况下是因为数据库登录审计的功能被启动了，此时一般建议把SYS.AUD$相关对象迁移到其它表空间，从而避免SYSTEM被用完的风险。
在Oracle 11g之前迁移方法如下所示：
1ALTER TABLE SYS.AUDIT$ MOVE TABLESPACE USERS;
2ALTER TABLE SYS.AUDIT_ACTIONS MOVE TABLESPACE USERS;
3ALTER TABLE SYS.AUD$ MOVE TABLESPACE USERS;
4ALTER TABLE SYS.AUD$ MOVE LOB(SQLBIND) STORE AS SYS_IL0000000384C00041$$ (TABLESPACE USERS);
5ALTER TABLE SYS.AUD$ MOVE LOB(SQLTEXT) STORE AS SYS_IL0000000384C00041$$ (TABLESPACE USERS);
6ALTER INDEX SYS.I_AUDIT REBUILD ONLINE TABLESPACE USERS;
7ALTER INDEX SYS.I_AUDIT_ACTIONS REBUILD ONLINE TABLESPACE USERS;

从Oracle 11g开始可以使用DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION进行迁移：
1EXEC DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(AUDIT_TRAIL_TYPE=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,AUDIT_TRAIL_LOCATION_VALUE => 'USERS');


如下示例为授予审计的一些权限：
1GRANT AUDIT ANY TO LHR_TEST;
2GRANT AUDIT SYSTEM TO LHR_TEST;


在Oracle 11g之前通过手工清理的方式或自定义作业来定期清理SYS.AUD$表，如下：
1TRUNCATE TABLE SYS.AUD$;
2DELETE FROM SYS.AUD$ WHERE OBJ$NAME='EMP';


需要注意的是，如果AUD$表过大，那么直接TRUNCATE AUD$表，系统要立即释放大量的EXTENTS，会严重影响系统性能。可以通过如下2个步骤逐步释放EXTENTS：
①　清空数据并且保留原来的EXTENTS：
1TRUNCATE TABLE SYS.AUD$ REUSE STORAGE;


在这里，REUSE STORAGE是TRUNCATE的一个参数，表示保持原来的存储不变。一般情况下，SQL命令“TRUNCATE TABLE TABLE_NAME;”其实就是“TRUNCATE TABLE TABLE_NAME DROP STORAGE;”。DROP STORAGE是TRUNCATE TABLE的默认参数。
②　逐步回缩EXTENTS：
1ALTER TABLE SYS.AUD$ DEALLOCATE UNUSED KEEP 5000M;
2ALTER TABLE SYS.AUD$ DEALLOCATE UNUSED KEEP 2000M;
3……
4ALTER TABLE SYS.AUD$ DEALLOCATE UNUSED KEEP 10M;


需要注意的是，在执行的时候，可以根据实际情况调整每次回缩空间的大小。
若审计在OS和XML选项下进行手动删除审计文件。在Oracle 11g中通过DBMS_AUDIT_MGMT包下的子过程进行手动或定期清理。下面的过程可以迁移审计记录到USERS表空间：
1conn / as sysdba
2BEGIN
3  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(AUDIT_TRAIL_TYPE           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
4                                           AUDIT_TRAIL_LOCATION_VALUE => 'USERS');
5
6END;
7/


使用包DBMS_AUDIT_MGMT下的INIT_CLEANUP过程可以设置审计的清除间隔，还有很多其它的实用存储过程请参考官方文档。
& 说明：
有关审计的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2140644/
 


本文选自《Oracle程序员面试笔试宝典》，作者：小麦苗

==================================================================================================================
 
【干货来了|小麦苗IT资料分享】
★小麦苗DB职场干货：https://mp.weixin.qq.com/s/Vm5PqNcDcITkOr9cQg6T7w
★小麦苗数据库健康检查：https://share.weiyun.com/5lb2U2M
★小麦苗微店：https://weidian.com/?userid=793741433
★各种操作系统下的数据库安装文件（Linux、Windows、AIX等）：https://pan.baidu.com/s/1hqff3Evv6oj2-Tn87MpFkQ
★小麦苗分享的资料：https://share.weiyun.com/57HUxNi
★小麦苗课堂资料：https://share.weiyun.com/5fAdN5m
★小麦苗课堂试听资料：https://share.weiyun.com/5HnQEuL
★小麦苗出版的相关书籍：https://share.weiyun.com/5sQBQpY
★小麦苗博客文章：https://share.weiyun.com/5ufi4Dx
★数据库系列（Oracle、MySQL、NoSQL）：https://share.weiyun.com/5n1u8gv
★公开课录像文件：https://share.weiyun.com/5yd7ukG
★其它常用软件分享：https://share.weiyun.com/53BlaHX
★其它IT资料（OS、网络、存储等）：https://share.weiyun.com/5Mn6ESi
★Python资料：https://share.weiyun.com/5iuQ2Fn
★已安装配置好的虚拟机：https://share.weiyun.com/5E8pxvT
★小麦苗腾讯课堂：https://lhr.ke.qq.com/
★小麦苗博客：http://blog.itpub.net/26736162/
★OCP培训：https://mp.weixin.qq.com/s/2cymJ4xiBPtTaHu16HkiuA
★12c的OCP培训：https://mp.weixin.qq.com/s/hMLHlyjMHhLmA0xN4hLvfw
★OCM培训：https://mp.weixin.qq.com/s/7-R6Cz8RcJKduVv6YlAxJA
★高可用（RAC+DG+OGG）培训：https://mp.weixin.qq.com/s/4vf042CnOdAD8zDyjUueiw
★小麦苗课堂腾讯视频：http://v.qq.com/vplus/71f69a319a24c6808cd6e6189ae90664


==================================================================================================================