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

"Yvan GALAS" <yvan.galas_at_fr.adp.com> wrote in message news:b6um58$oji$1_at_pegase.atos-infogerance.fr...
> Yes sorry to be more precise :
>
> I assume that you are in 9i cause SEGMENT SPACE MANAGEMENT doesn't exist
> in 8i

I'm not "in" anything... I'm not the original poster.

>
> If you specify : EXTENT MANAGEMENT LOCAL AUTOALLOCATE (Be care of blob
> objects..)
>
> -- first you cannot specify a DEFAULT STORAGE CLAUSE

That is true for ALL locally managed tablespace, whether it is autoallocate or not. Proof:

SQL> create tablespace blah
  2 datafile 'd:\oracle\oradata\db9\blah01.dbf' size 5m   3 extent management local
  4 uniform size 64K
  5 default storage (initial 64K next 64K); create tablespace blah
*
ERROR at line 1:
ORA-25143: default storage clause is not compatible with allocation policy

The inability to declare a default storage clause has nothing to do with whether you declare the LMT to be autoallocate or uniform size, but is an intrinsic feature of ALL LMT's.

> -- second oracle activate SEGMENT SPACE MANAGEMENT AUTO with a inital
> extent of 1M (If your block size is 16K or more)

Oracle does NOT "activate" Segment space management auto under any circumstances, automatically. You have to declare that clause yourself, since 'segment space management manual' remains the default, even in 9i release 2.

However, it is true that *if* you select to use ASSM, and you have a 16K block size, then it does indeed give you a 1M extent in autoallocated LMT rather than anything else. To that extent, I was wrong to suggest ASSM has no impact whatsoever on extent sizes, and it is perfectly possible that it is indeed the ASSM use by our original poster that is causing the behaviour that he sees.

>
> If you specify LOCAL and want to allocate your DEFAULT STORAGE CLAUSE
> as usual you must specify SEGMENT SPACE MANAGEMENT MANUAL

Not true. Please see above. You can't specify a default storage clause for an LMT, period. Or are you, instead, talking about a real storage clause specified at the segment level? Because if so, that also is up in the air. Depending on what you ask for in a storage clause, you may or may not get what you ask for, because the tablespace's own allocation policy is what ultimately counts, and that again has nothing to do with the use or non-use of ASSM.

>
> All of this information is in ORACLE DATABASE CONCEPTS.

Which you might want to go back and have another look at. In particular, be clearer on what you mean by a 'default storage clause', and whether ASSM is related to the tablespace's extent allocation policy. ASSM and Uniform Size are two different things; ASSM and autoallocate are two different things. Autoallocate, in particular, has a rather clever extent sizing policy which means you might or might not get what you ask for in a storage clause.

But to re-iterate, yes the use of ASSM can also affect extent sizes for block sizes of 16K or higher, and I was wrong to suggest it wouldn't.

Regards
HJR Received on Tue Apr 08 2003 - 17:01:08 CDT