Re: how to release system tablespace space?

DA Morgan wrote:
> joel garry wrote:
> > wyys.cn_at_gmail.com wrote:
> >> cause advance replication doesn't work. a lot of data segment locate
> >> in sytem tablespace, so system tablespace space is much beeter than
> >> before, any ways to release system tablespace space? thanks in advance!
> >
> > You can only shrink a tablespace as far as there isn't any data. You
> > can find a script or use OEM tablespace map (on some versions) to see
> > where the data ends in the tablespace. There are more advanced things
> > one can do, but you want a good DBA in there if you are messing with
> > system. If your database isn't too big, it might even be worthwhile to
> > recreate it from scratch, especially if it has been migrated from DMT
> > to LMT.
> >
> > There is some dependence on version and platform.
> >
> > jg
>
> Look at the dbms_space built-in package. It has two procedures
> for identifying shrink candidates which may help with shrinking
> some segments.

True. While we're mentioning things I forgot in my previous reply, we should say "get stuff out of the system tablespace that shouldn't be there, and check that the default tablespace for all users exists, and that the tablespace_name exists and is not system in dba_tables and dba_indexes for all non-Oracle-default users!"

jg

--
@home.com is bogus.
Size matters.
http://www.signonsandiego.com/uniontrib/20060802/news_1b2condo.html