Deferred Segment Creation not Supported for Partitioned Tables in Oracle 11.2.0.1

This post was sponsored by IMPERVA

I tried to create a partitioned table with deferred segment creation in an Oracle 11.2.0.1 database.

First I tried to do so explicitly but this did not work:

SQL> create table partitioned_table

  2  (refno number)

  3  segment creation deferred

  4  partition by range (refno)

  5  (partition partition1 values less than (10)

  6   tablespace users,

  7   partition partition2 values less than (maxvalue)

  8   tablespace users)

  9  /

create table partitioned_table

*

ERROR at line 1:

ORA-14223: Deferred segment creation is not supported

for this table

SQL>

Then I tried to set the appropriate parameter at session level but when I created a partitioned table, I found that it had 2 segments:

SQL> alter session set deferred_segment_creation = true

  2  /

Session altered.

SQL> create table partitioned_table

  2  (refno number)

  3  partition by range (refno)

  4  (partition partition1 values less than (10)

  5   tablespace users,

  6   partition partition2 values less than (maxvalue)

  7   tablespace users)

  8  /

Table created.

SQL> select count(*) from dba_segments

  2  where segment_name = 'PARTITIONED_TABLE'

  3  /

  COUNT(*)

----------

         2

SQL>

However, when I logged in to an Oracle 11.2.0.4 database, I found that I was able to create a partitioned table with deferred segment creation. (I understand that this was introduced in Oracle 11.2.0.2 but have no database to check this on):

SQL> create table partitioned_table

  2  (refno number)

  3  segment creation deferred

  4  partition by range (refno)

  5  (partition partition1 values less than (10)

  6   tablespace users,

  7   partition partition2 values less than (maxvalue)

  8   tablespace users)

  9  /

Table created.

SQL>

As you would expect, the table had no segments:

SQL> select count(*) from dba_segments

  2  where segment_name = 'PARTITIONED_TABLE'

  3  /

  COUNT(*)

----------

         0

SQL>

...and, as I added data, partitions were only created when they were actually needed:

SQL> insert into partitioned_table values (1)

  2  /

1 row created.

SQL> select count(*) from dba_segments

  2  where segment_name = 'PARTITIONED_TABLE'

  3  /

  COUNT(*)

----------

         1

SQL> insert into partitioned_table values (10)

  2  /

1 row created.

SQL> select count(*) from dba_segments

  2  where segment_name = 'PARTITIONED_TABLE'

  3  /

  COUNT(*)

----------

         2

SQL>