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

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Tue, 27 Aug 2002 21:36:13 +1000
Message-ID: <3d6b63f0@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.

Besides, I'm the attractive one.

Regards
HJR Received on Tue Aug 27 2002 - 06:36:13 CDT