Posted by Let’s say I’ve got a partitioned table, and because New Year’s eve is coming around, I certainly don’t want to be called out at 12:01am because I forgot to add the required partition for the upcoming year 
.
Since 11g, I can sleep easy at night by using the INTERVAL partition scheme. Here’s my table
SQL> create table t ( x date, y int )
2 partition by range ( x )
3 interval ( numtoyminterval(1,'MONTH'))
4 (
5 partition p201412 values less than ( date '2015-01-01' )
6 );
Table created.
SQL> insert into t
2 select date '2015-01-01'+rownum, rownum
3 from dual
4 connect by level <= 330;
330 rows created.
SQL> commit;
Commit complete.
SQL> select partition_name from user_tab_partitions where table_name = 'T';
PARTITION_NAME
----------------------------------------------------------------------------
P201412
SYS_P20234
SYS_P20235
SYS_P20236
SYS_P20237
SYS_P20238
SYS_P20239
SYS_P20240
SYS_P20241
SYS_P20242
SYS_P20243
SYS_P20244
12 rows selected.
The problem is … man, do I hate those automatic names . I suppose I can fix them up later, but in the meantime, I’ll create my local index on the table, but I’ll get the names right whilst I do
SQL>
SQL> create index IX on T ( y )
2 local
3 (
4 partition P201412
5 partition P201501,
6 partition P201502,
7 partition P201503,
8 partition P201504,
9 partition P201505,
10 partition P201506,
11 partition P201507,
12 partition P201508,
13 partition P201509,
14 partition P201510,
15 partition P201511
16 )
17 ;
Index created.
So far so good… I created 12 partitions in my table, and I explicitly listed 12 partitions in my index. But you need to be careful with such an approach, because if you’ve got that script in (say) your source control system, then even though you’ve specified a LOCAL index, you have also (perhaps unknowingly) set of limit of 12 partitions on the index should be re-run that script. So if I was to drop that index and recreate with the unchanged script (or for example, you’ve done a datapump extraction of DDL etc), then you might get yourself into a jam if the table data changes.
SQL>
SQL>
SQL> drop index ix;
Index dropped.
SQL>
SQL> insert into t
2 values (sysdate,2000);
1 row created.
SQL>
SQL>
SQL> create index IX on T ( y )
2 local
3 (
4 partition P201412
5 partition P201501,
6 partition P201502,
7 partition P201503,
8 partition P201504,
9 partition P201505,
10 partition P201506,
11 partition P201507,
12 partition P201508,
13 partition P201509,
14 partition P201510,
15 partition P201511
16 )
17 ;
create index IX on T ( y )
*
ERROR at line 1:
ORA-14024: number of partitions of LOCAL index must equal that of the underlying table
Now my same DDL does not work, because there are 13 table partitions and I only listed 12 index partitions. The easy workaround is not to list the partitions at all.
SQL>
SQL> create index IX on T ( y )
2 local ;
Index created.
SQL>
SQL>
and perhaps have an renaming routine that can be applied after the fact.
Learning is not a spectator sport 
When creating a LOCAL Index on a Partitioned Table, I recommend NOT naming the Index Partitions at all. One might make the mistake of mismatched Partition names — a Table Partition “J” might end up with an Index Partition “K”. Worse if Table Partition “K” has an Index Partition named “J”.
If we want to be optimistic towards New Year’s eve, then maybe in the future Oracle will add the possibility to specify a kind of “naming template” for creating better controlled partition names.
For example, for range partitions such a template could naturally specify a deterministic expression
based on the partition’s high_value.
I’d like to take this opportunity to wish you a VERY HAPPY AND BRIGHT NEW YEAR 2017 :):)
with lots of luck, and keep up with your great work for the Oracle community :):)
Best Regards,
Iudith Mentzel
Connor,
will you or Chris or AskTom Team be sharing with us about your new year resolution for 2017?
like how Tom does it years ago. http://www.oracle.com/us/dm/kyteresolutions-1440386.html
or something like your last year https://connormcdonald.wordpress.com/2016/01/04/resolutions-for-2016/
Also, Did you manage to fulfil your last-years-resolutions ?
Very good point. I’ll be blogging about that soon