Oracle: Interval partitioned tables

create table tq84_interval_partition_number (
  id   number,
  txt  varchar2(10),
  dt   date
)
partition by range (id) 
interval (1000) (
  partition tq84_int_part_num_1 values less than (0)  
);

insert into tq84_interval_partition_number values (0, 'foo', null   );
insert into tq84_interval_partition_number values (1, 'bar', sysdate);
insert into tq84_interval_partition_number values (2, 'baz', null   );

insert into tq84_interval_partition_number values (1000, '*', null   );

insert into tq84_interval_partition_number values (99500, 'a', null   );
insert into tq84_interval_partition_number values (99501, 'b', null   );
insert into tq84_interval_partition_number values (99502, 'c', null   );
insert into tq84_interval_partition_number values (99503, 'd', null   );

exec dbms_stats.gather_table_stats(user, 'tq84_interval_partition_number');

select
  partition_name,
  num_rows,
  high_value,
  interval,
  segment_created
from
  user_tab_partitions
where
  table_name = 'TQ84_INTERVAL_PARTITION_NUMBER'
;
  
explain plan for select * from tq84_interval_partition_number where id = 2;
select * from table(dbms_xplan.display);

drop table tq84_interval_partition_number purge;

-- Interval partitions are much like range partitions, BUT they
-- can create new partitions automatically.

create table tq84_interval_partition (
  id   number,
  txt  varchar2(10),
  dt   date
)
partition by range (dt) 
interval (numtoyminterval(1, 'month')) (
  partition tq84_partition_1 values less than (date '2010-01-01')  
);

column high_value format a100

select partition_name, high_value from user_tab_partitions where table_name = 'TQ84_INTERVAL_PARTITION';

insert into tq84_interval_partition values (1, 'Part 1', date '1888-10-18');
select partition_name, high_value from user_tab_partitions where table_name = 'TQ84_INTERVAL_PARTITION';

insert into tq84_interval_partition values (2, 'New part', date '2010-08-13');
select partition_name, high_value from user_tab_partitions where table_name = 'TQ84_INTERVAL_PARTITION';

insert into tq84_interval_partition values (3, 'same part', date '2010-08-12');
select partition_name, high_value from user_tab_partitions where table_name = 'TQ84_INTERVAL_PARTITION';

insert into tq84_interval_partition values (4, 'anoth part', date '2011-02-20');
select partition_name, high_value from user_tab_partitions where table_name = 'TQ84_INTERVAL_PARTITION';

-- Select from specific partition:
--   1) name of partition is known
select * from tq84_interval_partition partition     (tq84_partition_1);
--   2) name of partition is unknown
select * from tq84_interval_partition partition for (date '2010-08-01');

drop table tq84_interval_partition purge;

The day that is used in the partitioning clause must not be greater than 28, otherwise, Oracle throws an ORA-14767: Cannot specify this interval with existing high bounds error.

drop   table tq84_char_interval_partition purge;

--
--  Create a table that is partitioned based upon the value of a text/character field (month_6).
--  The character field must contain a month in the yyyymm format.
--  The virtual and hidden column partition_nr is numeric and counts the months since
--  January 2000. Therefore, it can be used to partition the table.
--
create table tq84_char_interval_partition (
  id      number primary   key,
  month_6 char    ( 6) not null,
  col_1   varchar2(10) not null,
  col_2   varchar2(10),
  --
  partition_nr number invisible generated always as (months_between(to_date(month_6, 'yyyymm'), date '2000-01-01'))
)
partition by
  range   (partition_nr)
  interval(           1) (
    partition tq84_char_interval_p0 values less than (0)
  )
;


insert into tq84_char_interval_partition values (1, '201707', 'foo', 'bla'     );
insert into tq84_char_interval_partition values (2, '201707', 'bar', 'more bla');
insert into tq84_char_interval_partition values (3, '201103', 'baz',  null     );

select *                 from tq84_char_interval_partition;
select p.*, partition_nr from tq84_char_interval_partition p;

select * from tq84_char_interval_partition partition for (210);
select * from tq84_char_interval_partition partition for (months_between(date '2017-07-01', date '2000-01-01'));