Splits a range (RANGE) or list (LIST) partition into two new partitions. This operation reorganizes data and is irreversible. Before you run this command, plan the new partition boundaries and names, and back up your data.
Synopsis
-- Split a RANGE partition
ALTER TABLE table_name SPLIT PARTITION partition_name
AT (range_part_value)
INTO
(
PARTITION new_part1
[TABLESPACE tablespace_name],
PARTITION new_part2
[TABLESPACE tablespace_name]
);
-- Split a LIST partition
ALTER TABLE table_name SPLIT PARTITION partition_name
VALUES (value[, value]...)
INTO
(
PARTITION new_part1
[TABLESPACE tablespace_name],
PARTITION new_part2
[TABLESPACE tablespace_name]
);
Parameters
Parameter | Required | Description | Example |
table_name
| Yes | The name of the target partitioned table. | log_archives
|
partition_name
| Yes | The name of the existing partition to split. | p_2022
|
AT (range_part_value)
| For splitting a RANGE partition | Defines the split point for a RANGE partition. This value becomes the lower boundary of the second new partition. | AT (TO_DATE('2022-07-01', 'YYYY-MM-DD'))
|
VALUES (value[, value]...)
| For splitting a LIST partition | Defines the list of values to assign to the first new partition when you split a LIST partition. | VALUES ('China', 'Singapore')
|
INTO (...)
| Yes | Specifies the names of the two new partitions and an optional tablespace. new_part1 contains data before the split point, and new_part2 contains data after the split point. | INTO (PARTITION p_h1, PARTITION p_h2)
|
TABLESPACE tablespace_name
| No
Default value: the default tablespace of the table | Specifies the storage tablespace for the new partitions. You can specify different or the same tablespaces for the two new partitions. | TABLESPACE tbs_archive
|
Notes
You must be the table owner or have the ALTER permission for the table.
SPLIT PARTITION acquires an AccessExclusiveLock, which is a table-level exclusive lock. This lock blocks all Data Manipulation Language (DML) and most Data Definition Language (DDL) operations on the table. Run this command during off-peak hours and allow a sufficient time window.
The split point must be valid:
For a RANGE partition, the value in the AT clause must be between the upper and lower bounds of the partition to split.
For a LIST partition, the list of values in the VALUES clause must be a subset of the value list of the partition to split.
Examples
Split a range (RANGE) partition
This example shows how to split an annual log partition named p_2022 into two semi-annual partitions named p_2022_h1 and p_2022_h2.
Prepare the environment
This step creates a table named log_archives that is partitioned by time range, and inserts data that spans the split point.
-- Create a partitioned table.
CREATE TABLE log_archives (
log_id INT NOT NULL,
log_time DATE NOT NULL
)
PARTITION BY RANGE (log_time) (
PARTITION p_2022 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD'))
);
INSERT INTO log_archives VALUES (1, TO_DATE('2022-01-10', 'YYYY-MM-DD'));
INSERT INTO log_archives VALUES (2, TO_DATE('2022-02-15', 'YYYY-MM-DD'));
INSERT INTO log_archives VALUES (3, TO_DATE('2022-03-20', 'YYYY-MM-DD'));
INSERT INTO log_archives VALUES (4, TO_DATE('2022-05-05', 'YYYY-MM-DD'));
INSERT INTO log_archives VALUES (5, TO_DATE('2022-06-25', 'YYYY-MM-DD'));
INSERT INTO log_archives VALUES (6, TO_DATE('2022-07-15', 'YYYY-MM-DD'));
INSERT INTO log_archives VALUES (7, TO_DATE('2022-08-01', 'YYYY-MM-DD'));
INSERT INTO log_archives VALUES (8, TO_DATE('2022-10-30', 'YYYY-MM-DD'));
INSERT INTO log_archives VALUES (9, TO_DATE('2022-11-11', 'YYYY-MM-DD'));
INSERT INTO log_archives VALUES (10, TO_DATE('2022-12-24', 'YYYY-MM-DD'));
Perform the pre-check
Before you split the partition, ensure that your data is backed up and check whether the original partition exists.
-- Important: Before you split the partition, make sure that you have backed up the relevant data.
SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'LOG_ARCHIVES';
Run the command
Use 2022-07-01 as the split point and run the SPLIT PARTITION command.
ALTER TABLE log_archives SPLIT PARTITION p_2022
AT (TO_DATE('2022-07-01', 'YYYY-MM-DD'))
INTO (PARTITION p_2022_h1, PARTITION p_2022_h2);
Verify the result
Verify that the original partition is replaced by two new partitions, and that the data is correctly distributed.
-- Verify the structure: Confirm that the new partitions are created and the old partition is removed.
SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'LOG_ARCHIVES';
-- The query result should contain p_2022_h1 and p_2022_h2, but not p_2022.
-- Verify the data: Confirm that the data is distributed based on the split point.
SELECT COUNT(*) FROM log_archives PARTITION (p_2022_h1); -- The result should be 5.
SELECT COUNT(*) FROM log_archives PARTITION (p_2022_h2); -- The result should be 5.
Split a list (LIST) partition
This example shows how to split an Asian sales partition named p_asia, which contains multiple countries and regions, into a separate China partition named p_china, and another partition named p_asia_others for other Asian countries and regions.
Prepare the environment
This step creates a table named sales_by_region that is partitioned by a list of sales regions.
-- Create a partitioned table.
CREATE TABLE sales_by_region (
sale_id INT,
country VARCHAR2(20)
)
PARTITION BY LIST (country) (
PARTITION p_asia VALUES ('China', 'Japan', 'Korea', 'Singapore')
);
INSERT INTO sales_by_region VALUES (101, 'China');
INSERT INTO sales_by_region VALUES (102, 'China');
INSERT INTO sales_by_region VALUES (201, 'Japan');
INSERT INTO sales_by_region VALUES (301, 'Korea');
INSERT INTO sales_by_region VALUES (401, 'Singapore');
Perform the pre-check
Check whether the p_asia partition exists and check its data distribution.
SELECT PARTITION_NAME, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'SALES_BY_REGION';
SELECT country, COUNT(*) FROM sales_by_region GROUP BY country;
Run the command
Use the VALUES clause to split the data for 'China' from the p_asia partition into a new partition named p_china.
ALTER TABLE sales_by_region SPLIT PARTITION p_asia
VALUES ('China')
INTO (PARTITION p_china, PARTITION p_asia_others);
Verify the result
Verify that the original partition is replaced by two new partitions, that their value lists are correctly assigned, and that the data is migrated accordingly.
-- Verify the structure: Confirm that the new partitions and their value lists are created.
SELECT PARTITION_NAME, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'SALES_BY_REGION';
-- The query result should contain:
-- P_CHINA, 'China'
-- P_ASIA_OTHERS, 'Japan', 'Korea', 'Singapore'
-- Verify the data: Confirm that the data is distributed to the new partitions.
SELECT COUNT(*) FROM sales_by_region PARTITION (p_china); -- The result should be 2.
SELECT COUNT(*) FROM sales_by_region PARTITION (p_asia_others); -- The result should be 3.
FAQ
Q1: Why do I receive the error ORA-14080: partition cannot be split?
This error usually occurs because the split point you defined is invalid. For a RANGE partition, the value in the AT clause must be between the upper and lower bounds of the partition being split. For a LIST partition, the list of values in the VALUES clause must be a non-empty and proper subset of the value list of the partition being split.
Q2: Why do I receive the error ORA-14078: partition name is already in use?
This error occurs because the new partition name that you specified in the INTO clause is already used by another partition in the table. Change the name to one that is unique within the table.
Q3: Why is SPLIT PARTITION slow?
This operation involves reorganizing and copying physical data. The execution time is proportional to the amount of data in the partition and generates a large amount of I/O. This is a resource-intensive operation. Run this operation during off-peak hours.