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

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.

--
Sybrand Bakker, Senior Oracle DBA