Oracle9i Automatic Space Management - a "feature"?

From: Don Burleson <don_at_burleson.cc>
Date: 26 Aug 2002 16:00:09 -0700
Message-ID: <998d28f7.0208261500.5595eecf@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:00:09 CDT