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

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:3d6b63f0_at_dnews.tpgi.com.au...
> Telemachus wrote:
>
> > 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?
> >> >
> >>
> >>
>
> Nah. Richard things ASSM is OK, and I don't.

The more I see of ASSM, the less I like ...

>
> Besides, I'm the attractive one.

And this is going into my list of Oracle Myths ;)

You wish

Richard

>
> Regards
> HJR
Received on Tue Aug 27 2002 - 07:34:27 CDT