The Geek Diary

Oracle Database 18c : How to Merge Partitions And Subpartitions Online

by

This post is a short note on merging partitions and subpartitions online. The below sample demonstration shows you how to merge partitions online while there are concurrent transactions going on. For the purpose of this post, we will create a sample table and load some sample data into it to perform the merge.

1. Create A table

Let’s create a sample table “onlinemergepartition” as shown below.

--- Creating a table
CREATE TABLE onlinemergepartition
(
pid number,
salesdate date,
price number
)
PARTITION BY RANGE(salesdate)
(
PARTITION saleq116 VALUES LESS THAN (TO_DATE('01-APR-2016', 'DD-MON-YYYY')),
PARTITION saleq216 VALUES LESS THAN (TO_DATE('01-JUL-2016', 'DD-MON-YYYY')),
PARTITION saleq316 VALUES LESS THAN (TO_DATE('01-OCT-2016', 'DD-MON-YYYY')),
PARTITION saleq416 VALUES LESS THAN (TO_DATE('01-JAN-2017', 'DD-MON-YYYY')),
PARTITION saleq117 VALUES LESS THAN (TO_DATE('01-APR-2017', 'DD-MON-YYYY')),
PARTITION saleq217 VALUES LESS THAN (TO_DATE('01-JUL-2017', 'DD-MON-YYYY')),
PARTITION saleq317 VALUES LESS THAN (TO_DATE('01-OCT-2017', 'DD-MON-YYYY')),
PARTITION saleq417 VALUES LESS THAN (TO_DATE('01-JAN-2018', 'DD-MON-YYYY')),
PARTITION salefuture VALUES LESS THAN (TO_DATE('01-JAN-2020', 'DD-MON-YYYY'))
)
ENABLE ROW MOVEMENT
/

2. Load Sample data

Once you have create the sample table, you can load some sample data into it. Gather the stats for the table to verify.

-- Load some sample data
-- Gather the Stats
exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'PARTUSER', TABNAME=>'ONLINEMERGEPARTITION', ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, CASCADE=> TRUE, METHOD_OPT=> 'FOR ALL COLUMNS SIZE 1', GRANULARITY=>'ALL');

3. Merging Partitions Online

1. From one session check the artition Names, Partition Positions, Interval & High Value. We will merge the partition ‘saleq116’ and ‘saleq216’ into the partition ‘saleq216’ as shown below:

-- In New Session-1
-- SESS1:
-- Check the Partition Names, Partition Positions, Interval & High Value
select table_name, partition_name, partition_position, interval, high_value from user_tab_partitions where table_name='ONLINEMERGEPARTITION' order by table_name, partition_position;
alter table onlinemergepartition merge partitions saleq116, saleq216 into partition saleq216 online;

2. From a different session we will set the partition ranges:

-- In New Session-2
-- SESS2:
update onlinemergepartition set salesdate='01-JUN-16' where pid=116;
commit;

Verify

Go to Session 1 and check whether merge is successful and also cross verify the partition ranges.

-- SESS1:
select table_name, partition_name, partition_position, interval, high_value from user_tab_partitions 
where table_name='ONLINEMERGEPARTITION' order by table_name, partition_position;

You May Also Like