In the partitioning table operation, when a partition already has the largest partition, how do you extend the partition again? The simple way is to delete the maximum partition first, then add the required partition and build the maximum partition again, if it is not possible for the system that is in the 7x24 hour. What's the way? Here's 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 (' 2013-01-01 ', ' yyyy-mm-dd ')),
Partition p_2014 values less than (to_date (' 2014-01-01 ', ' yyyy-mm-dd ')),
Partition P_max values less than (MaxValue)
);
sql> INSERT into test values (1,to_date (' 2012-10-01 ', ' yyyy-mm-dd '));
sql> INSERT into test values (2,to_date (' 2013-10-01 ', ' yyyy-mm-dd '));
sql> INSERT into test values (3,to_date (' 2014-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 January-October-12
Sql> SELECT * FROM Test partition (p_2014);
ID record_date
---------- --------------
2 January-October-13
Sql> SELECT * FROM Test partition (P_max);
ID record_date
---------- --------------
3 January-October-14
Sql> ALTER TABLE Test add partition p_2015 values
Less Than (to_date (' 2015-10-01 ', ' yyyy-mm-dd '));
ALTER TABLE test ADD partition p_2015 values
*
An error occurred on line 1th:
ORA-14074: The partition bounds must be adjusted above the last partition boundary
sql> ALTER TABLE test split partition P_max at (to_date (' 2015-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 (' 2015-10-01 ', ' yyyy-mm-dd '));
Sql> commit;
Sql> SELECT * FROM Test partition (P_2015);
ID record_date
---------- --------------
3 January-October-14
Sql> SELECT * FROM Test partition (P_max);
ID record_date
---------- --------------
4 January-October-15
Oracle Online Extended partition