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

Hi Howard,

Spooky ;)

Richard
"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:3d6adb61_at_dnews.tpgi.com.au...
> 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 Tue Aug 27 2002 - 01:38:02 CDT