关闭

oracle 11g的audit导致system表空间快速增长的问题

标签: systemoracletabledeletedictionaryobject
8704人阅读 评论(0) 收藏 举报
分类:

11gr2版本,oracle把参数audit_trail 自动设置为DB级别,导致很多数据库的操作被记录在审计表sys.aud$中,导致sys.aud$所在的表空间快速增长。可以通过TRUNCATE清空改表,同时,为了system表空间的安全,建议把改表转移至别的表空间

SQL> show parameter  audit_trail

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

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:481271次
    • 积分:5996
    • 等级:
    • 排名:第4353名
    • 原创:131篇
    • 转载:1篇
    • 译文:2篇
    • 评论:25条
    文章分类
    最新评论