Re: import from 8.05 to 9i, query regarding system tablespace size

Sybrand Bakker <gooiditweg_at_sybrandb.verwijderdit.demon.nl> wrote in message news:<tpjdm01us24ovn4mm34h0ihos7pnofino3_at_4ax.com>...
> On 8 Oct 2004 08:37:10 -0700, richardpstanton_at_yahoo.com (Richard
> Stanton) wrote:
>
> >Hello all
> >
> >I have imported data from an 8.05 database into a brand new empty 9i
> >database and have a question about the system tablespace.
> >
> >In the original database the system tablespace was set at 80mb, and is
> >half-full. All I did was to import one user's tables, about 500mb,
> >and in the 9i database, system has autoextended up to 400mb, and is
> >99% full. Is this normal?
> >
> >It may have not been the right thing to do, but in my ignorance I
> >allowed the wizard to create it with default parameters.
> >
> >Can anyone tell me whether I need to worry?! Should I tear it all
> >down and start again?
> >
> >thanks in advance
> >
> >Richard
>
> Two problems:
> 1
>
> You've exported the 8.0.5 data with the default COMPRESS=Y, which will
> make sure a segment of 100 extents of 10k will end up in 1 extent of
> 1M, causing free space fragmentation in the target tablespace, causing
> the tablespace to extend when the datafile has been set to autoextend
>
> 2
> You should never ever create end-user objects in the SYSTEM
> tablespace.
>
> So yes, if I would have to do it, I would start over, create a
> database with multiple tablespaces and relocate the end-users tables.

Thanks for your reply. Forgot to mention I did compress=n. Also, the objects imported all belonged to one user who I created with a custom tablespace. Is there a quick way to see what's gone into system?

Richard Received on Sat Oct 09 2004 - 10:06:18 CDT