| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: import from 8.05 to 9i, query regarding system tablespace size
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
> 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 Received on Mon Oct 11 2004 - 05:05:42 CDT
 |
 |