Oracle-L: Re:Moving Autit Tables

From: <dgoulet_at_vicr.com>
Date: Tue, 28 May 2002 13:28:28 -0800
Message-ID: <F001.0046D538.20020528132828@fatcity.com>

>From Metalink:

                 Bookmark
                                                         Default Font
                                                                                
                   Go to End


  Doc ID: 
         Note:1019377.6
  Subject: 
         Script to move SYS.AUD$ table out of SYSTEM tablespace
  Type: 
         SCRIPT
  Status: 
         PUBLISHED

                                                               Content Type: 
                                                                                
 TEXT/PLAIN
                                                               Creation Date: 
                                                                                
 02-JUL-1996
                                                               Last Revision
Date:

18-MAR-2002 Disclaimer:

This script is provided for educational purposes only. It is NOT supported by Oracle World Wide Technical Support. The script has been tested and appears to work as intended. However, you should always test any script before relying on it.

Moreover, you should be aware that moving AUD$ out of SYSTEM tablespace is *not* a supported procedure. Oracle does not support changing ownership of AUD$, or any triggers on it. For a complete discussion on this topic see Note:72460.1

Abstract:

  Oracle stores audit trail records in the SYS.AUD$ base data dictionary table.   The problem is that this table grows inside the SYSTEM tablespace and must have
  records deleted from it or be truncated, otherwise it will take up all the room
  in the SYSTEM tablespace. This deleting and truncating of the SYS.AUD$ table   will fragment the system tablespace.

  The following script allows a DBA to move SYS.AUD$ out of the SYSTEM tablespace.
  By moving it out of system tablespace, the table's size can be controlled without
  filling or fragmenting the system tablespace.

Requirements:

This script should be run by the SYS user or as connect internal.

Script:

cut ---------------------- cut -------------- cut --------------

  SET ECHO off
  REM NAME: TFSAUDMV.SQL
  REM USAGE:"@path/tfsaudmv.sql"

  REM --------------------------------------------------------------------------

REM REQUIREMENTS:
REM Should be tun as SYS or connect internal

  REM --------------------------------------------------------------------------

  REM AUTHOR:  
  REM    Scott Gossett         
  REM --------------------------------------------------------------------------

  REM PURPOSE:
  REM The purpose of this script is to move the existing SYS.AUD$ table   REM and its associated index I_AUD1 to a different tablespace.   REM This script creates a new tablespace AUD that will be used to   REM hold both objects. The example file size is too small for production   REM environment!!
  REM

  REM EXPLANATION:
  REM Oracle stores audit trail records in the SYS.AUD$ base data dictionary   REM table. The problem is this table grows inside the SYSTEM tablespace   REM and must have records deleted from it or be truncated, otherwise it   REM takes up all the room in the system tablespace. This deleting and   REM truncating of the SYS.AUD$ table fragments the system tablespace.   REM
  REM The following script allows a DBA to move SYS.AUD$ out of the SYSTEM   REM tablespace. By moving it out of system tablespace, control of the   REM table's size can be controlled without filling or fragmenting the   REM system tablespace.
  REM

REM DISCLAIMER:
REM This script is provided for educational purposes only. It is NOT REM supported by Oracle World Wide Technical Support. REM The script has been tested and appears to work as intended. REM You should always run new scripts on a test instance initially.

  REM --------------------------------------------------------------------------

REM Main text of script follows:

create tablespace "AUDIT"

     datafile '$HOME/data/aud01.dbf' size 500k 
        default storage (initial 100k next 100k pctincrease 0)

/
create table audx tablespace "AUDIT"

     storage (initial 50k next 50k pctincrease 0) 
        as select * from aud$ where 1 = 2

  /
  rename AUD$ to AUD$$
  /
  rename audx to aud$
  /
  create index i_aud1
    on aud$(sessionid, ses$tid)
      tablespace "AUDIT" storage(initial 50k next 50k pctincrease 0)   /

cut ---------------------- cut -------------- cut --------------

Remark:

Advice to not use reserved words as audit for tablespace as in example

Reference:

Note 98859.1: How to Determine Reserved Words in 8i Note:72460.1: Moving AUD$ to another tablespace and adding triggers to AUD$ .

Dick Goulet

____________________Reply Separator____________________
Author: "Guidry; Chris" <chris.guidry_at_atcoelectric.com>
Date:       5/28/2002 12:38 PM

Hi All,
There has been some discussion in the past regarding moving the audit tables out of the SYSTEM table space to avoid fragmentation. Is this a good idea and if so does any have a procedure for doing so? Are there any problems to watch out for?

O7345 - WinNT - RAID 0+1

--
Chris J. Guidry  P.Eng. EE
ATCO Electric, Metering Services
Phone: (780) 420-4142
Fax: (780) 420-3854
Email: chris.guidry_at_atcoelectric.com


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Guidry, Chris
  INET: chris.guidry_at_atcoelectric.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: dgoulet_at_vicr.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue May 28 2002 - 16:28:28 CDT