oracle 11g的audit导致system表空间快速增长的问题
来源:互联网 发布:js 隐藏控件读值 编辑:程序博客网 时间:2024/05/22 03:22
11gr2版本,oracle把参数audit_trail 自动设置为DB级别,导致很多数据库的操作被记录在审计表sys.aud$中,导致sys.aud$所在的表空间快速增长。可以通过TRUNCATE清空改表,同时,为了system表空间的安全,建议把改表转移至别的表空间
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string DB
TRUNCATE TABLE sys.aud$;
由于sys.aud$有lob字段,将审计结果表从system表空间里移动到别的表空间上的操作具体如下实际上sys.aud$表上包含了两个lob字段,并不是简单的move table就可以。
下面是具体的过程:
alter table sys.aud$ move tablespace monitor;
alter table sys.aud$ move lob(sqlbind) store as( tablespace monitor);
alter table sys.aud$ move lob(SQLTEXT) store as( tablespace monitor);
TRUNCATE TABLE sys.aud$;
附录:关于SYS.AUD$的删除操作
a) To delete rows from the database audit trail table, an appropriate privilege
is required. You must either be the user SYS, or a user with DELETE ANY TABLE
system privilege, or a user to whom SYS has granted the object privilege
DELETE on SYS.AUD$.
b) To purge audit records from the audit trail, delete all rows:
-- DELETE FROM sys.aud$;
To delete rows from the audit trail related to a particular audited table:
-- DELETE FROM sys.aud$ WHERE obj$name='';
c) If audit trail information must be archived, copy the relevant rows to another
table before deletion, using either:
-- CREATE TABLE
AS SELECT * from sys.aud$ WHERE 1=2;
-- INSERT INTO
SELECT FROM sys.aud$
-- EXPort the to an OS file, but do not export SYS.AUD$
directly.
d) Reducing the size of the audit trail:
1. If you want to save information currently in the audit trail, copy it to
another table and optionally export that table.
2. SQL> connect / as sysdba
SQL> TRUNCATE TABLE sys.aud$;
Truncate uses the DROP STORAGE clause which keeps 'minextents' extents,
thus only 1 extent.
3. Reload archived audit trail records generated from Step 1.
The rows inserted require as many extents as necessary to contain current
audit trail rows, but no more.
CAUTION: SYS.AUD$ is the only SYS object that should ever be directly modified
The DELETE ANY TABLE privilege only applies to SYS objects if O7_DICTIONARY_ACCESSIBILITY=TRUE
- oracle 11g的audit导致system表空间快速增长的问题
- oracle 11g的audit导致system表空间快速增长的问题
- oracle 11g 表空间的存储文件错误,导致数据库不能启动
- 由于audit.d导致的服务器空间问题解决
- oracle 11g的表空间加密
- oracle 11g 表空间大小问题
- using audit in oracle database 11g
- 日志快速增长导致磁盘撑爆的快速解决方法
- Oracle system表空间的恢复
- oracle system表空间的管理
- Oracle 11g笔记——表空间的体系结构
- Audit Login 导致死机的原因
- ORACLE的数据库审计 audit
- Oracle表空间文件损坏导致的数据库异常关闭并启动失败问题的解决方法
- Oracle审计功能开启导致SYSTEM表空间不足
- 电子商务快速增长的特点
- ORA-01078: failure in processing system parameters 问题的解决方法(oracle 11g)
- oracle 11g 导入数据库,表空间超过32G的解决办法
- ORA-01017/ORA-02063DbLink建立错误问题分析及解决
- clearsilver-C-API文档(HDF部分)
- random
- Red Hat Package Manager(RPM的介绍和应用)
- [Android]为Spinner填充数据后设置默认值的问题
- oracle 11g的audit导致system表空间快速增长的问题
- 11g R2 RAC客户端负载均衡配置
- 谷歌浏览器打不开12306
- ubuntu 中软件重新安装卸载等操作
- [LeetCode] 3Sum
- UILabel 根据文字长度自动调整
- php逻辑操作符中&和&&的异同
- 动态规划解题法
- 喝水老呛的原因及。。。