• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

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

by admin

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;

Filed Under: 18c, oracle

Some more articles you might also be interested in …

  1. What are the .lok files used in WebLogic
  2. How to upgrade RMAN catalog SCHEMA from 11g to 12.1.0.2 without upgrading the catalog database
  3. Oracle 20c New Feature: PDB Point-in-Time Recovery or Flashback to Any Time
  4. Oracle ASM 12c – New Features with examples
  5. What are different Oracle Database Vault Roles
  6. PL/SQL: Factorial Program
  7. How to Export and Import Data Guard Broker Configuration in Oracle 19c
  8. Oracle Interview Questions : Using srvctl V/s sqlplus and pfile V/s spfile in RAC
  9. How to get the Values Assigned by Default to a Profile in Oracle Database
  10. How to find redo log members/redo log file size/redo log status

You May Also Like

Primary Sidebar

Recent Posts

  • aws ec2: CLI for AWS EC2 (Command Examples)
  • aws cur – Create, query, and delete AWS usage report definitions (Command Examples)
  • aws configure – Manage configuration for the AWS CLI (Command Examples)
  • aws cognito-idp: Manage Amazon Cognito user pool and its users and groups using the CLI

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright