>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).