Sunday, September 04, 2016

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>

1 comment:

Hammed said...

This content creates a new hope and inspiration with in me. Thanks for sharing article like this. The way you have stated everything above is quite awesome. Keep blogging like this.

SEO Company in Chennai