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

Hi Don,

Yes, you are missing something.

Not sure where to start with this so I guess the beginning might be the place to go.

Firstly, note your first create table is *syntactically* incorrect (as the error message suggests). PCTFREE and PCTUSED are *not* storage clause options and need to be specified accordingly. You will get this error message regardless of the type of tablespace.

Next, the FREELISTS, FREELIST GROUPS and PCTUSED and the only options *ignored*. Note these parameters are ignored and do not generate errors so that existing create table scripts and the such are compatible and will work regardless of the type of tablespace. I don't see this as being an issue to Oracle professionals as they would presumably have a reasonable understanding of both the characteristics of a specified tablespace and how such characteristics apply to objects in the tablespace (if they don't then I question the "professional" bit of Oracle professional)

PCTFREE is still a valid option and is required for the growth of existing rows in the blocks. This is just as important as ever for LMT with ASSM.

NEXT is not entirely ignored in that if the MINEXTENTS of the table were to be 2 or greater, then the NEXT storage clause (in combination with PCTINCREASE if 3 or more MINEXTENTS) is used to calculate the initial size of the object and the appropriate number of extents are hence initially allocated. Once allocated, then yes NEXT becomes irrelevant.

"Your Honour, Automatic Segment Space Management already has a bad enough reputation in some circles without these unjustified accusations being levelled against them and I request that these charges be dropped immediately"

"Not guilty" ;)

Richard

"Don Burleson" <don_at_burleson.cc> wrote in message news:998d28f7.0208261500.5595eecf_at_posting.google.com...
> 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
>
>
> While Oracle9i rejects the PCTFREE and PCTUSED parameter with locally
> managed tablespaces with automatic space management, 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 ) ;
>
> 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.
>
> Am I missing something?
Received on Mon Aug 26 2002 - 18:42:23 CDT