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

Yes ...

Stop it at once ... you're not twins separated at birth..... are you ?

<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 - 05:19:09 CDT