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

richardpstanton_at_yahoo.com (Richard Stanton) wrote in message news:<87bc8a5f.0410110205.1025d195_at_posting.google.com>...
> Sybrand Bakker <sybrandb_at_hccnet.nl> wrote in message news:<6v5gm05npul2tb18ifbn0jaencp871fb4v_at_4ax.com>...
> > On 9 Oct 2004 08:06:18 -0700, richardpstanton_at_yahoo.com (Richard
> > Stanton) wrote:
> >
> > >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
> >
> >
> > select segment_name, segment_type
> > from dba_segments
> > where owner = '<your user>'
> > and tablespace_name = 'SYSTEM'
> >
> > I suspect though it will be pretty much the complete user.
> > That could be verified easily
> >
> > select default_tablespace
> > from dba_users
> > where username=....
> > /
> > select *
> > from dba_sys_privs
> > where privilege = 'UNLIMITED TABLESPACE'
> > and grantee = ....
> > /
> > select *
> > from dba_ts_quotas
> > where tablespace_name = .....
> >
> > If both the last 2 queries show no records, and the first query shows
> > the correct tablespace, you don't have privilege and imp will put
> > everything in system.
>
> Hi
>
> I ran your sql's and got the following back:
>
> select segment_name, segment_type
> from dba_segments
> where owner = '<your user>'
> and tablespace_name = 'SYSTEM'
>
> gave me no rows.
>
> > select default_tablespace
> > from dba_users
> > where username=....
>
> DEFAULT_TABLESPACE
> ------------------------------
> GALAXY_TRAIN
>
> which is correct
>
> > select *
> > from dba_sys_privs
> > where privilege = 'UNLIMITED TABLESPACE'
> > and grantee = ....
>
> GRANTEE PRIVILEGE ADM
> ------------------------------ ---------------------------------------- ---
> GALAXY UNLIMITED TABLESPACE NO
>
> > select *
> > from dba_ts_quotas
> > where tablespace_name = .....
>
> no rows selected
>
> Does this give any clue as to what I have fouled up?!
>
> thanks again for your help, much appreciated.
>
> Richard

Just as a post-script, I created another database using the same defaults for everything, and while it was empty I had a look at the system tablespace.

It has a current size of 400Mb and is 99% used. Is that normal for 9i?

Richard Received on Tue Oct 12 2004 - 11:27:50 CDT