11G brought interval partitioning which is a new partitioning method to ease the maintenance burden of adding new partitions manually. The interval partition clause in the create table statement has an option to list tablespace names to be used for interval partitioning. The documentation states that the tablespaces in the list you provide are used in a round-robin manner for new partitions:
Interval partitions are created in the provided list of tablespaces in a round-robin manner.
This does not mean that any newly created partition will reside in the tablespace which is next on the list. The tablespaces may be skipped if partitions map to more than one interval. Here is a test case that shows how the list is used.
set lines 200
SQL> r
1 create table t(col1 date,col2 varchar2(100))
2 partition by range (col1)
3 interval(numtoyminterval(1,'MONTH')) store in (tbs1,tbs2,tbs3)
4* (PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2009', 'DD-MM-YYYY')) tablespace tbs1)
Table created.
SQL> r
1 select PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME
2* from user_Tab_partitions where table_name='T'
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------
P0 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS1
The "store in" clause lists tablespaces tbs1, tbs2 and tbs3 to be used for interval partitioning. After the above create table command I now have one partition which resides in tbs1. Let's insert a row which needs to be inserted into a new partition and see which tablespace the partition will be created in.
SQL> insert into t values(to_date('15.01.2009','dd.mm.yyyy'),'jan');
1 row created.
SQL> commit;
Commit complete.
SQL> select PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME
2 from user_Tab_partitions where table_name='T';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------
P0 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS1
SYS_P61 TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS2
The row I inserted maps to one interval, which is one month, it does not have a date value which is more than one month higher than the current maximum value. So the next tablespace, tbs2, is used for the new partition.
SQL> insert into t values(to_date('15.02.2009','dd.mm.yyyy'),'feb');
1 row created.
SQL> commit;
Commit complete.
SQL> select PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME
2 from user_Tab_partitions where table_name='T';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------
SYS_P62 TO_DATE(' 2009-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS3
P0 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS1
SYS_P61 TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS2
Again I inserted a row for the next month and the partition is created in tbs3, which is the next one on the list.
What happens if I insert a row with a date value that is more than one month after the current maximum partitioning key?
SQL> insert into t values(to_date('15.04.2009','dd.mm.yyyy'),'apr');
1 row created.
SQL> commit;
Commit complete.
SQL> select PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME
2 from user_Tab_partitions where table_name='T';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------
SYS_P62 TO_DATE(' 2009-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS3
P0 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS1
SYS_P61 TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS2
SYS_P63 TO_DATE(' 2009-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS2
I skipped March and inserted a value for April. The current maximum key becomes May 1st, we do not see a partition with a maximum value of Apr 1st. The next tablespace on the list was tbs1 but we see that the new partition is on tbs2, not tbs1. Tbs1 would be used if I did not skip an interval when inserting rows.
So, the tablespaces on the list are used in a round-robin manner but each is used for only one interval. If you skip intervals the tablespaces related to that interval are skipped too.
This is something to keep in mind if you want to strictly decide which tablespace will hold which partition.
