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

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.

--
Sybrand Bakker, Senior Oracle DBA