Oracle online extended partitions

Oracle online extended partitions

In the Oracle Partition Table operation, when a partition already has the largest partition, how can we expand the partition again? The simple method is to delete the maximum partition first, then add the required partition, and create the maximum partition again. If the system is 7*24 hours old, it may not work. How can this problem be solved? Here is an experiment:

SQL> select * from v $ version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bi
PL/SQL Release 10.2.0.1.0-Production
CORE 10.2.0.1.0 Production
TNS for 64-bit Windows: Version 10.2.0.1.0-Production
NLSRTL Version 10.2.0.1.0-Production

SQL> drop table test purge;
SQL> create table test
(
Id number,
Record_date date
) Partition by range (record_date)
(
Partition p_2013 values less than (to_date ('1970-01-01 ', 'yyyy-MM-dd ')),
Partition p_2014 values less than (to_date ('1970-01-01 ', 'yyyy-MM-dd ')),
Partition p_max values less than (maxvalue)
);

SQL> insert into test values (1, to_date ('2017-10-01 ', 'yyyy-MM-dd '));
SQL> insert into test values (2, to_date ('2017-10-01 ', 'yyyy-MM-dd '));
SQL> insert into test values (3, to_date ('2017-10-01 ', 'yyyy-MM-dd '));
SQL> commit;
SQL> select partition_name from user_tab_partitions
Where table_name = 'test'
Order by partition_position;
PARTITION_NAME
------------------------------
P_2013
P_2014
P_MAX

SQL> select * from test partition (p_2013 );
ID RECORD_DATE
------------------------
1 month-10 month-12

SQL> select * from test partition (p_2014 );
ID RECORD_DATE
------------------------
2 01-10 months-13

SQL> select * from test partition (p_max );
ID RECORD_DATE
------------------------
3 01-10-14

SQL> alter table test add partition p_2015 values
Less than (to_date ('2017-10-01 ', 'yyyy-MM-dd '));
Alter table test add partition p_2015 values
*
Row 3 has an error:
ORA-14074: The partition limit must be adjusted above the last partition limit

SQL> alter table test split partition p_max at (to_date ('2017-01-01 ', 'yyyy-MM-dd '))
Into (partition p_2015, partition p_max) update global indexes;

SQL> select partition_name from user_tab_partitions
Where table_name = 'test'
Order by partition_position;
PARTITION_NAME
------------------------------
P_2013
P_2014
P_2015
P_MAX

SQL> insert into test values (4, to_date ('2017-10-01 ', 'yyyy-MM-dd '));
SQL> commit;
SQL> select * from test partition (p_2015 );
ID RECORD_DATE
------------------------
3 01-10-14

SQL> select * from test partition (p_max );
ID RECORD_DATE
------------------------
4 months-15

Introduction to range partitions of Oracle partition tables

Oracle Partition Table migration

Oracle Partition Table instance

Create and manage Oracle Partition tables