Oracle Online Extended partition

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.