Re: Oracle9i Automatic Space Management - a "feature"?

Don Burleson wrote:

> This command is using an LMT with automatic space management:
>
> SQL> create table
> 2 test_table
> 3 (c1 number)
> 4 tablespace
> 5 asm_test
> 6 storage
> 7 ( pctfree 20 pctused 30 )
> 8 ;
>
> ( pctfree 20 pctused 30 )
> *
> ERROR at line 7:
> ORA-02143: invalid STORAGE option
>

Yup: PCTFREE and PCTUSED are *not* part of the storage clause at all. Never have been, never will...

>
> While Oracle9i rejects the PCTFREE and PCTUSED parameter with locally
> managed tablespaces with automatic space management,

It most certainly does *not* "reject" them. It rejected them in this particular case because your syntax was way up the Swanee. PCTFREE is a perfectly valid setting for ASSM, just as it was for FLM (free list managed) segments.

>it does allow you
> to enter invalid settings for NEXT and FREELISTS settings:
>
> SQL> create table
> 2 test_table
> 3 (c1 number)
> 4 tablespace
> 5 asm_test
> 6 storage
> 7 ( freelists 30 next 5m ) ;

Nothing particularly invalid there, either: it's documented that FREELISTS when specified will be ignored for ASSM segments, otherwise every piece of software out there would probably break. NEXT has nothing to do with ASSM, but with the extent sizes allocated to a segment, and if your complaint is that this is a LMT, and hence NEXT is meaningless... well, you're half way up the Swanee there too. NEXT *is* "respected but not observed" when a segment is first created in a LMT. Say you demand INITIAL 1M, NEXT 10M and then say MINEXTENTS 2... it's obvious you want 11Mb of storage for this segment, so Oracle divides that by the extent sizes the tablespace can actually allocate (say, 1Mb) and thus gives you that number of extents (in this case 11).

It works the same with INITIAL, by the way: you set INITIAL to 10M, in a 1Mb LMT, and you'll be allocated 11 1Mb extents.

>
> Table created.
>
> This could be a serious issue for the Oracle professional unless they
> remember that locally-managed tablespaces with automatic space
> management ignore any specified values for NEXT and FREELISTS.
>

Erm, well, yes, I guess it could be. But on the other hand, these things are documented a-plenty, and can thus just be considered 'the way things are', and any Oracle professional worth her salt would (reasonably, I think) be expected to know that.

It can also equally validly be said that any decent Oracle professional will want her head examined if she goes about using ASSM when she's not running a RAC.

:-)
Regards
HJR
> Am I missing something?
Received on Mon Aug 26 2002 - 20:53:15 CDT