Partition by range with Interval example

SQL> select min(time_id) from sh.sales;

MIN(TIME_ID)
--------------------
1998-JAN-01 00:00:00

SQL> create table sales
  2  partition by range (time_id)
  3  interval (numtoyminterval(1,'MONTH'))
  4  (
  5   partition p199801 values less than (to_date('1998-02-01','yyyy-mm-dd'))
  6  )
  7  as select * from sh.sales;

Table created.

SQL> create index sales_n1 on sales(time_id) local;

Index created.

SQL> exec dbms_stats.gather_table_stats('','sales');

PL/SQL procedure successfully completed.

SQL> set lin 120
SQL> col partition for a10
SQL> col high_value for a80
SQL> col num_rows for 99999999
SQL> set pages 999


SQL> select partition_name,high_value,num_rows
  2  from user_tab_partitions
  3  where table_name='SALES';

  PARTITION_ HIGH_VALUE                                                                        NUM_ROWS
---------- -------------------------------------------------------------------------------- ---------
P199801    TO_DATE(' 1998-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     15132
SYS_P69    TO_DATE(' 1998-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     14307
SYS_P70    TO_DATE(' 1998-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     14248
SYS_P71    TO_DATE(' 1998-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     11818
SYS_P72    TO_DATE(' 1998-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     12309
SYS_P73    TO_DATE(' 1998-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     11631
SYS_P74    TO_DATE(' 1998-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     16257
SYS_P75    TO_DATE(' 1998-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     17199
SYS_P76    TO_DATE(' 1998-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     17059
SYS_P77    TO_DATE(' 1998-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     19878
SYS_P79    TO_DATE(' 1998-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     14612
SYS_P78    TO_DATE(' 1999-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     14384
SYS_P80    TO_DATE(' 1999-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     20437
SYS_P81    TO_DATE(' 1999-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     24122
SYS_P82    TO_DATE(' 1999-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     19627
SYS_P83    TO_DATE(' 1999-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     17004
SYS_P84    TO_DATE(' 1999-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     19213
SYS_P85    TO_DATE(' 1999-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     18016
SYS_P86    TO_DATE(' 1999-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     21889
SYS_P87    TO_DATE(' 1999-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     22225
SYS_P88    TO_DATE(' 1999-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     23024
SYS_P89    TO_DATE(' 1999-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     22256
SYS_P91    TO_DATE(' 1999-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     21259
SYS_P90    TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     18873
SYS_P92    TO_DATE(' 2000-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     22135
SYS_P93    TO_DATE(' 2000-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     20609
SYS_P94    TO_DATE(' 2000-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     19453
SYS_P95    TO_DATE(' 2000-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     17481
SYS_P96    TO_DATE(' 2000-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     20046
SYS_P97    TO_DATE(' 2000-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     17988
SYS_P98    TO_DATE(' 2000-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     18534
SYS_P99    TO_DATE(' 2000-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     20369
SYS_P100   TO_DATE(' 2000-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     20047
SYS_P101   TO_DATE(' 2000-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     21542
SYS_P102   TO_DATE(' 2000-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     19709
SYS_P103   TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     14733
SYS_P104   TO_DATE(' 2001-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     20739
SYS_P105   TO_DATE(' 2001-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     19185
SYS_P106   TO_DATE(' 2001-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     20684
SYS_P107   TO_DATE(' 2001-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     21667
SYS_P108   TO_DATE(' 2001-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     19188
SYS_P109   TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     22437
SYS_P110   TO_DATE(' 2001-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     21860
SYS_P112   TO_DATE(' 2001-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     23330
SYS_P111   TO_DATE(' 2001-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     20579
SYS_P115   TO_DATE(' 2001-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     24537
SYS_P113   TO_DATE(' 2001-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     22403
SYS_P114   TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     22809

48 rows selected.