>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$
.
Copyright (c) 1995,2000 Oracle Corporation. All Rights Reserved. Legal
Notices and Terms of Use.
Dick Goulet
____________________Reply Separator____________________
Author: "Guidry; Chris" <[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Guidry, Chris
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
