Skip to content
July 3, 2010 / doganay

CHANGING TABLESPACE OF SYS.AUD$

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

Hi,

The default tablespace of sys.aud$ table is system. If you enable auditing in your database and your sys.aud$ table is growing enourmously,

you may want to change the tbs of sys.aud$. To do this:

1) You can drop and re-create your sys.aud$.  But in this method some objects might be invalid. So, you may need to run

SQL> @?/rdbms/admin/utlrp

It’s useful to backup create script of sys.aud$ before dropping it, but do not worry 🙂 here is a sample create script of sys.aud$.

After dropping it, create it on some other tbs (ANY TBS YOU WANT, but not system or sysaux please 🙂  )

CREATE TABLE sys.AUD$
TABLESPACE system
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
INITIAL          64K
MINEXTENTS       1
MAXEXTENTS       UNLIMITED
PCTINCREASE      0
FREELISTS        1
FREELIST GROUPS  1
BUFFER_POOL      DEFAULT
)
LOGGING
NOCOMPRESS
LOB (SQLTEXT) STORE AS
( TABLESPACE  system
ENABLE      STORAGE IN ROW
CHUNK       16384
RETENTION
NOCACHE
INDEX       (
TABLESPACE system
STORAGE    (
INITIAL          64K
NEXT             1
MINEXTENTS       1
MAXEXTENTS       UNLIMITED
PCTINCREASE      0
FREELISTS        1
FREELIST GROUPS  1
BUFFER_POOL      DEFAULT
))
STORAGE    (
INITIAL          64K
MINEXTENTS       1
MAXEXTENTS       UNLIMITED
PCTINCREASE      0
FREELISTS        1
FREELIST GROUPS  1
BUFFER_POOL      DEFAULT
)
)
LOB (SQLBIND) STORE AS
( TABLESPACE  system
ENABLE      STORAGE IN ROW
CHUNK       16384
RETENTION
NOCACHE
INDEX       (
TABLESPACE system
STORAGE    (
INITIAL          64K
NEXT             1
MINEXTENTS       1
MAXEXTENTS       UNLIMITED
PCTINCREASE      0
FREELISTS        1
FREELIST GROUPS  1
BUFFER_POOL      DEFAULT
))
STORAGE    (
INITIAL          64K
MINEXTENTS       1
MAXEXTENTS       UNLIMITED
PCTINCREASE      0
FREELISTS        1
FREELIST GROUPS  1
BUFFER_POOL      DEFAULT
)
)
NOCACHE
NOPARALLEL
MONITORING;
2) You can move it to another tbs, but it is painful in most circumstances. Because you have to move LOBs , etc. also.
So, I recommend 1st method.
alter table sys.aud$ move tablespace MY_TABLESPACE;

try and pray 🙂

Advertisements

One Comment

Leave a Comment
  1. doganay / Oct 12 2010 11:53 am

    Hi,

    If you want to try 2nd method, you can use these:

    ALTER TABLE SYS.AUD$ MOVE TABLESPACE NEW_TBS;
    alter table aud$ move lob (sqlbind) store as (tablespace NEW_TBS);
    alter table aud$ move lob (sqltext) store as (tablespace NWE_TBS);
    ALTER INDEX SYS.I_AUD1 REBUILD TABLESPACE NEW_TBS;

    try and pray 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: