remove AUDIT table from SYSTEM TABLESPACE

[tekpr00]

Hello,
I was wondering how could one safely remove a SYS.AUD$ table from SYSTEM tablespaces.
1) is there a move_table command that one can use from one tablespace to the other. OR
2)create table AS select statement will work and then drop SYS.AUD in the SYSTEM tablespace.

My question is which one will require no downtime on the database.

 

[LKBrwnDBA]

Connect as sysdba, then you would need to not only move the table, but also the LOB storage as well.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

PS: For option 1, Try something like this:

ALTER TABLE sys.aud$ MOVE TABLESPACE  sysaux;

ALTER TABLE sys.aud$ MOVE
LOB (sqlbind) STORE AS (TABLESPACE  sysaux)
LOB (sqltext) STORE AS (TABLESPACE  sysaux);

ALTER INDEX sys.i_aud1 REBUILD TABLESPACE sysaux;

For option 2 (unless you want to keep the data in AUD$), you could just drop and re-create the table.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[tekpr00]

Thanks for your input.
Can this be done while the database is online.
Is this going to put the SYSTEM tablespace(source) off line

 

[LKBrwnDBA]

Yes, it can be done while db is online.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[tekpr00]

I created the target tablespace as AUDIT_TBS with about 250M in size.

I used the command
ALTER TABLE sys.aud$ MOVE TABLESPACE AUDIT_TBS.

IT came down with an error -
ORA-01652: unable to extend temp segment by 1048 in tablespace AUDIT_TBS.

Remember, the target tablespace is 250M and the size of sys.aud$ is only 60K.

I have reserched the error and every suggestion is to increase AUDIT_TBS. But it already has more than enough.

 

[LKBrwnDBA]

Remember, the target tablespace is 250M and the size of sys.aud$ is only 60K.

How do you know it's 60K? How much is allocated (as opposed to used)? What about the CLOB storage allocated (as opposed to used)?

What about your AUDIT_TBS tablespace, is it locally managed? What space management? Uniform extents? Initial extent? etc...

If there are no rows (you want to keep) in the SYS.AUD$ table, then just drop it and re-create in the new tablespace.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

PS: Did you create AUDIT_TBS as TEMPORARY TABLESPACE????

ORA-01652: unable to extend temp segment by 1048 in tablespace AUDIT_TBS.

That is a No-no, you need permanent tablespace (DUH!).



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[tekpr00]

It was created as permanent tablespace.(duh!)