Re: Export / Import from 8i to 9i and locally managed tablespace

"achkar" <kachkar_at_tablimited.com.au> wrote in message news:acb78eab.0304072233.50ee4c98_at_posting.google.com...
> Hi All ,
>
> I have a table on oracle 8i (8.1.7 ), this table resides on dictionary
> managed tablespace: initial size 256k , next size 256k, that is for
> both the table and the tablespace .
> then I did export to that table with no merge.
> after that I created locally managed tablespace on 9i with uniform
> allocation 520k ,segment space management is automatic
>
> When I did the import I noticed that the initial size for that table
> on 9i is 1096K instead of 520K , Why is that? , is it because 'segment
> space management is automatic '

Please stop using ASSM (automatic segment space management). Check out the www.google.com archives for this group, and you will see a ream of posts from me and others on the subject. ASSM is a brilliant way of curing freelist contention, which is commonly going to occur in a Real Application Cluster environment. But unless you are suffering from freelist contention, then it has extremely large costs and extremely nasty side effects, and it just isn't worth it.

As to your specific problem, I would imagine it might have something to do with the export having used COMPRESS=Y, which means that on subsequent import, the table will be created with a single initial extent sized to be the total size of all extents existing at the time of the export. Now, if your table had comprised two extents, that would have meant the import would have attempted to create a segment with an initial extent of 512K. Your tablespace can only allocate extents of 520K (and that, incidentally, is an extremely odd extent size, and you'd do better in the future to stick to 64K, 1M, 8M and 64M extents).

Now ordinarily, I'd expect a request for 512k to be satisfied with the allocation of a single 520K extent. But if you're using ASSM, then Oracle also has to create the necessary bit map blocks to handle the chore of assigning row inserts to an appropriate block. It is quite possible that the bitmap blocks tipped the 512K request into a 528K request... at which point, the tablespace realises you want more than the 520K it can allocate, and therefore gives you two lots of extents.

> and if so , how can I stop that ,

Two things. Take control of the export process so that it doesn't do COMPRESS=Y. And stop using ASSM when it's not appropriate to do so.

> I would like to know what will
> happen if my table is really big ( 20 million record ) , what will
> happen to the initial size for that table when I do the import. or do
> I need to create the table on 9i then do the import.

If you take control of the export process, then you'll get what you ask for. But if you let export do its thing, then you'll get 'peculiar' results. Be in charge.

Regards
HJR
>
> any help / opinion / thoughts will be appreciated.
Received on Tue Apr 08 2003 - 06:38:38 CDT