8i - 9i conversion tablespaces much larger after import

[davo]

hi,
we are converting our databases from 8i to 9i.
We are using locally managed ts with uniform 1m.
The tablespaces on the 9i are much larger than the 8i after we import the data - and some are showing 100% used
e.g. one tablespace is used 70meg sized at 400meg so 17% used in 8i …. And after import into 9i with size 400 meg and specifying UNIFORM extent growth of 1meg, it is 100% used and is taking up 452 meg ??? - is this anything to do with uniform, or is it something to do with 9i tablespaces? - we would have to allocate a lot more disk space if this is going to happen, and we do not have a lot of spare space presently. I just do not understand why the tablespace has so much more used space in 9i version - should I have chosen the compress option in the exp/imp etc and would this have made any difference to the physical data anyway?

here is the create tablespace command
CREATE TABLESPACE ENTPER
DATAFILE SIZE 400M
AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 1M
ONLINE
PERMANENT;

also, should we make the SYSTEM tablespace locally managed or leave it Dictionary managed?

[pando]

did u use compress=n in ur export?

[John Spencer]

If you still have the old 8i database around, run the following query:

SELECT segment_name, segment_type, blocks, extents
FROM dba_segments
WHERE tablespace_name = 'YOUR_TBS'
ORDER BY blocks

My guess is that you will find a number (probably a large number) of segments that take only a few blocks in the old database. With your UNIFORM LMT, every segment will take at least 1 MB of space whether it needs it or not.

You have two options. Either create another UNIFORM LMT with a much smaller extent size (perhaps 64K) and move all of the small segments into that tablespace, or if you want to have only a single tablespace, then make it auto allocate. The first few extents of every segment will be small (64K), and the extent sizes will grow as the number of extents increases.

HTH
John

[davo]

thanks for the replies.
Think I will change it to autoallocate.
Presumably I can do this with a command - can you tell me what it is?

I am a newbie dba.

No I left the default in my export (which is Y)

Future scripts for creation will be changed too.

p.s. what is your opinion on SYSTEM tablespace - should it be locally or dictionary managed?

[pando]

If you use compress=y then you will get into this type of problems

Always use compress=n