ITPub博客

首页 > Linux操作系统 > Linux操作系统 > How to Truncate, Delete, or Purge Rows from the Audit Trail Table SYS.AUD$

How to Truncate, Delete, or Purge Rows from the Audit Trail Table SYS.AUD$

原创 Linux操作系统 作者:ilsyx 时间:2011-08-19 13:52:59 0 删除 编辑

个人总结

在10gR2以后如果不用DBMS_AUDIT_MGMT这个包管理审计信息的话.全语句直接操作SYS.AUD$表就行. 按照自己参考的工作文档,记得将SYS.AUD$表移出system表空间就行.
How to Truncate, Delete, or Purge Rows from the Audit Trail Table SYS.AUD$ [ID 73408.1]

  Modified 11-SEP-2010     Type BULLETIN     Status PUBLISHED  

Applies to:

Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 11.2.0.1 - Release: 8.1.7 to 11.2
Information in this document applies to any platform.
Checked for relevance on 11-Sep-2010

Purpose

This document explains how to purge, truncate, or delete rows from the audit trail table SYS.AUD$. Starting with Oracle 10gR2 it is possible to use package DBMS_AUDIT_MGMT for this. More information about this package can be read in Note 731908.1 -New Feature DBMS_AUDIT_MGMT To Manage And Purge Audit Information.

Scope and Application

This document is intended for DBA's or Oracle Support Analysts.

How to Truncate, Delete, or Purge Rows from the Audit Trail Table 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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11780477/viewspace-705352/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-06-12

  • 博文量
    196
  • 访问量
    608595