Donald K. Burleson Oracle Tips

Inconsistent treatment of object storage parameters in Oracle9i

Oracle has recently introduced two new tablespace parameters that automate storage management functions:

• Locally Managed Tablespaces (LMT) – The LMT tablespace is implemented by adding EXTENT MANAGEMENT LOCAL clause to the tablespace definition.  LMT tablespaces automates extent management and remove the ability to specify the NEXT storage parameter.

• Automatic Space Management (ASM) – The ASM tablespace is implemented by adding the SEGMENT SPACE MANAGEMENT AUTO clause to the tablespace definition.  ASM tablespaces automate freelist management and remove the ability to specify PCTFREE, PCTUSED, FREELISTS and FREELIST GROUPS storage parameters.

Here is an example of a tablespace with these new parameters:

create tablespace

   asm_test

datafile

   'c:\oracle\oradata\diogenes\asm_test.dbf'

size

   5m

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO

;

In Oracle9i, we expect an error if we try to specify PCTFREE or PCTUSED for a table defined inside a tablespace 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

However, here we see an important point.  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.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link: