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.
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>
