How to convert partition table into Interval Partitioning table in 11g

1. Create dummy tablespaces and user:
create tablespace testa11_data datafile size 10m autoextend on maxsize 2000m;
create tablespace testa11_index datafile size 10m autoextend on maxsize 2000m;

create tablespace testb11_data datafile size 10m autoextend on maxsize 2000m;

create user vj identified by vj
default tablespace testa11_data;
grant connect, resource to vj;


2. Create a partition table and local index:
connect vj/vj
drop table emp;
create table emp(eno number(1), ename varchar2(10), sal number(6), join_date date)
PARTITION BY RANGE (join_date) (
PARTITION emp_dec06 VALUES LESS THAN (TO_DATE('01-JAN-2007','DD-MON-YYYY'))
TABLESPACE testa11_data,
PARTITION emp_jan07 VALUES LESS THAN (TO_DATE('01-FEB-2007','DD-MON-YYYY'))
TABLESPACE testa11_data,
PARTITION emp_feb07 VALUES LESS THAN (TO_DATE('01-MAR-2007','DD-MON-YYYY'))
TABLESPACE testa11_data);

create index idx1_eno_emp on emp(eno)
local
tablespace testa11_index;


3. Check and note the partition and tablespace names:
select * from emp;

col table_name for a20;
col partition_name for a20;
col tablespace_name for a20;
col index_name for a20;
select table_name, partition_name, tablespace_name
from dba_tab_partitions
where table_name = 'EMP';

select index_name, partition_name, tablespace_name
from dba_ind_partitions
where index_name = 'IDX1_ENO_EMP';


4. Insert data:
insert into emp values(1,'VJ',100,'20-JAN-2007');
insert into emp values(2,'BJ',200,'20-FEB-2007');
insert into emp values(3,'JJ',300,'20-MAR-2007'); -- this insert will fail, because there is no March data partition.
insert into emp values(4,'PJ',400,'20-APR-2007'); -- this insert will fail, because there is no April data partition.
commit;


5. -- 11g trick
alter table emp set interval(numtoyminterval(1,'MONTH')); -- Setting up the interval for the table.
alter table emp set store in (testb11_data); -- All upcoming partitions data will be assigned to new tablespace (testb11_data).

insert into emp values(3,'JJ',300,'20-MAR-2007');
commit;

insert into emp values(4,'PJ',400,'20-APR-2007');
commit;

select table_name, partition_name, tablespace_name
from dba_tab_partitions
where table_name = 'EMP';

select index_name, partition_name, tablespace_name
from dba_ind_partitions
where index_name = 'IDX1_ENO_EMP';


create tablespace testb11_index datafile size 10m autoextend on maxsize 2000m;

alter index idx1_eno_emp modify DEFAULT ATTRIBUTES tablespace testb11_index; -- Assigning new tablespace for new index partitions.

insert into emp values(6,'RJ',500,'20-JUN-2007');
commit;


Summary: Great 11g partition feature improvement. The thing I like about this is, not only creates new table and index partitions and also allocates existing index tablespace to index partitions. No rebuild index partitions required to modify the correct tablespace!!!