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>

How to List Chained Rows in a Partioned Table

I will be looking at partititioning and chaining elsewhere but this simple example shows how you can look for chained rows in a partitioned table. First create a partitioned table:

SQL> create table andrew

  2  partition by range (owner)

  3  (partition p1 values less than ('M'),

  4   partition p2 values less than (maxvalue))

  5  as select * from dba_tables

  6  /

 

Table created.

 

SQL> 

Then look for any chained rows in it. If a table is partitioned , you have 2 options. You can analyze the whole table or you can analzye individual partitions:

SQL> analyze table andrew list chained rows

  2  /

 

Table analyzed.

 

SQL> analyze table andrew partition(p1)

  2  list chained rows

  3  /

 

Table analyzed.

 

SQL> 

As you might expect, if you specify a partition which does not exist, you get an appropriate error message:

SQL> analyze table andrew partition(does_not_exist)

  2  list chained rows

  3  /

analyze table andrew partition(does_not_exist)

                               *

ERROR at line 1:

ORA-02149: Specified partition does not exist

 

SQL>

You also get an error if you try to use this syntax on a non-partitioned table:

SQL> create table fred

  2  as select * from dba_objects

  3  /

 

Table created.

 

SQL> analyze table fred

  2  partition(in_non_partitioned_table)

  3  list chained rows

  4  /

analyze table fred

              *

ERROR at line 1:

ORA-14501: object is not partitioned

 

SQL>