Re: Altering system tablespace or system cluster

"Christian Olivares" <mutrax_at_hotmail.com> wrote in message news:shxn6.1885$O1.8904_at_wagner.videotron.net...
> Hi,
>
> I have this 8.1.6 test database on my WinNT workstation. I activated the
> audit system in order to do some security research and after a couple of
> days the SYSTEM tablespace went from 500MB to 4.5GB. I stopped the audit
> and cleaned the AUD$ table but I still can't squeeze back the TS size to
> it's original 500MB.
>
> The problem is caused by a single extent that belongs to the cluster
 C_TS#.
> Extent 3 of this cluster is located in segment 33433 and extent 4 is in
> segment 570993. With 8k segments, that means that I have a gap of 4.3GB
 of
> unused space.
>
> I can't drop the cluster nor alter it since it's a system component.
>
> Other than doing a full export and reinstalling the database, Is there
 any
> other way to reduce the TS size ?
>

Not that this helps much, but you must never switch on auditing until you've moved the AUD$ table out to a separate tablespace somewhere (alter table AUD$ move tablespace blah). You then have to rebuild an index on that table (the name of which escapes me off-hand). That technique (along with the index name) is in the documentation.

As for fixing the problem up -I think you've just learned the hard way why the advice is always loud and strong: never allow anything to start writing to the SYSTEM tablespace in bulk. Export won't help you any, since export cannot and will not export objects in SYS's schema. You could conceivably try 'move tablespace' commands (because a table can be moved into the same tablespace as it currently occupies, and the move command therefore re-organises and re-builds the thing), but index invalidation after a move is a killer, and you'd have to find out very carefully what indexes were on that table. And if it's a cluster, I doubt that the move command will actually work anyway.

Regards
HJR
> Thanks in advance !!
>
> Christian Olivares
> Québec.
>
>
>
Received on Fri Mar 02 2001 - 16:35:15 CST