Oracle online extended partitions and oracle online extended partitions

Oracle online extended partitions and oracle online extended partitions

In the 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
How does an oracle Partition Table automatically add partitions every month?

In oracle, If Automatic tasks are involved, the job method is generally used.
1. Write a stored procedure by yourself. The purpose of this stored procedure is to add a partition to the partition table.
2. Create a new Job and call the stored procedure you write at a certain time every month.
In this way, you can automatically add partitions every month.

Oracle Database Partition Table backup

Create table par_tab (
Data_date varchar2 (8 ),
Col_n varchar2 (20)
)
Partition by range (data_date )(
Partition part_201000 values less than ('20140901') tablespace tab_201000,
Partition part_201001 values less than ('20140901') tablespace tab_201003,
Partition part_201002 values less than ('20140901') tablespace tab_201003,
Partition part_201003 values less than ('20140901') tablespace tab_201003,
Partition part_201004 values less than ('20140901') tablespace tab_201006,
Partition part_201005 values less than ('20140901') tablespace tab_201006,
Partition part_201006 values less than ('20140901') tablespace tab_201006,
Partition part_201007 values less than ('20140901') tablespace tab_201009,
Partition part_201008 values less than ('20140901') tablespace tab_201009,
Partition part_201009 values less than ('20140901') tablespace tab_201009,
Partition part_201010 values less than ('20140901') tablespace tab_201012,
Partition part_201011 values less than ('20140901') tablespace tab_201012,
Partition part_201012 values less than ('20140901') tablespace tab_201012,
Partition part_201099 values less than ('20140901') tablespace tab_201099,
Partition part_201100 values less than ('20140901') tablespace tab_201000,
Partition part_201101 values less than ('20140901') tablespace tab_201003,
Partition part_201102 values less than ('20140901') tablespace tab_201003,
Partition part_201103 valu ...... remaining full text>