Oracle flexibly allows you to use a combination of interval and reference partitioning.
For example, if you create an interval range partitioned parent table, as follows:
SQL> create table orders(order# number primary key,order_date timestamp,data varchar2(30))PARTITION BY RANGE (order_date)INTERVAL (numtoyminterval(1,’year’))

(PARTITION part_2020 VALUES LESS THAN (to_date(’01-01-2021′,’dd-mm-­yyyy’)) , PARTITION part_2021 VALUES LESS THAN (to_date(’01-01-2022′,’dd-mm-­yyyy’)));
Table created.

Next is the code to create a reference partitioned child table:
SQL> create table order_line_items( order# number,line# number,data varchar2(30),constraint c1_pk primary key(order#,line#),constraint c1_fk_p foreign key(order#) references orders)partition by reference(c1_fk_p);Table created.

To see interval reference partitioning in action, let’s insert some data. First, we insert rows that will fit within existing range partitions:
SQL> insert into orders values (1, to_date( ’01-jun-2020′, ‘dd-mon-yyyy’ ), ‘xxx’); 1 row created.
SQL> insert into orders values (2, to_date( ’01-jun-2021′, ‘dd-mon-yyyy’ ), ‘xxx’); 1 row created.
SQL> insert into order_line_items values( 1, 1, ‘yyy’ ); 1 row created.
SQL> insert into order_line_items values( 2, 1, ‘yyy’ ); 1 row created.

All of the prior rows fit into the partitions specified when creating the tables. The following query displays the current partitions:

SQL> select table_name, partition_name from user_tab_partitions where table_name in ( ‘ORDERS’, ‘ORDER_LINE_ITEMS’ ) order by table_name, partition_name;TABLE_NAME PARTITION_NAME
ORDERS PART_2020
ORDERS PART_2021
ORDER_LINE_ITEMS PART_2020
ORDER_LINE_ITEMS PART_2021

Next, rows are inserted that don’t fit into an existing range partition; therefore, Oracle automatically creates partitions to hold the newly inserted rows:
SQL> insert into orders values (3, to_date( ’01-jun-2022′,’dd-mon-yyyy’ ), ‘xxx’);1 row created.
SQL> insert into order_line_items values (3, 1, ‘zzz’ ); 1 row created.

The following query shows that two interval partitions were automatically created, one for the parent table and one for the child table:

SQL> select a.table_name, a.partition_name, a.high_value, decode( a.interval, ‘YES’, b.interval ) interval from user_tab_partitions a, user_part_tables b where a.table_name IN (‘ORDERS’, ‘ORDER_LINE_ITEMS’) and a.table_name = b.table_nameorder by a.table_name;

TABLE_NAME PARTITION_ HIGH_VALUE INTERVAL
—————— ———- ——————————— ——–
ORDERS PART_2020 TIMESTAMP’ 2021-01-01 00:00:00′
ORDERS PART_2021 TIMESTAMP’ 2022-01-01 00:00:00′
ORDERS SYS_P1640 TIMESTAMP’ 2023-01-01 00:00:00′ NUMTOY
MINTERVAL
(1,’YEAR’)
ORDER_LINE_ITEMS ORDER_LINE_ITEMS ORDER_LINE_ITEMS
PART_2020
PART_2021
SYS_P1640 YES

Two partitions named SYS_P1640 were created, with the parent table partition having a high value of 2023-01-01. If desired, you can rename the partitions via the ALTER TABLE command:
SQL> alter table orders rename partition sys_p1640 to part_2022; Table altered.
SQL> alter table order_line_items rename partition sys_p1640 to part_2022; Table altered.

Tip See the “Interval Partitioning” section of this chapter for an example of automating the renaming of partitions via PL/SQL.