Merging Partitions Online

Before You Begin

This 5-minute tutorial shows you how to merge partitions online.

Background

Before Oracle Database 18c, merging partitions required an exclusive lock on the relevant partitions for the entire duration of the operation.
In Oracle Database 18c, you can complete a MERGE partition maintenance operation which is often used in rolling up all old partitions into a single partition and then archiving them, online.

What Do You Need?

Oracle Database 18c installed
A container database (CDB) and a pluggable database (PDB)
The dumpfile exp_sh_pdb1.dmp with SH.SALES data to import into SH.SALES partitioned table. Download exp_sh_pdb1.dmp to the labs directory created on your server /home/oracle/labs.
You will use two independent console sessions in this OBE.

Merge Partitions Online

SH

user.

sqlplus system@PDB1
Enter password: password

Create the SH user.

DROP USER sh CASCADE;
CREATE USER sh IDENTIFIED BY password;

Grant the user the DBA role and read and write privilege on the DP_PDB1 logical directory.
```
GRANT dba TO sh;
```
```
CREATE DIRECTORY dp_pdb1 as '/home/oracle/labs';
GRANT read, write ON DIRECTORY dp_pdb1 TO sh;
```
Create the RANGE partitioned table SH.SALES from code1.
Quit the session.
```
EXIT
```

Import rows with your dump file.

cd /home/oracle/labs

$ORACLE_HOME/bin/impdp sh@PDB1 directory=dp_pdb1 dumpfile=exp_sh_pdb1.dmp remap_tablespace=USERS:SYSTEM table_exists_action=replace
Password: password

Ignore the error GRANT SELECT ON "SH"."SALES" TO "BI". Ignore the five errors on ALTER TABLE SALES ... FOREIGN KEY.

Log in to PDB1 as SYSTEM to create the SH.I2_PROMO_ID on the PROMO_ID column.
```
sqlplus system@pdb1
Enter password: password
```

Create the SH.I2_PROMO_ID on the


                    PROMO_ID

column.

DROP INDEX SH.SALES_PROMO_BIX;
DROP INDEX sh.i2_promo_id;
CREATE INDEX sh.i2_promo_id ON sh.sales (promo_id) GLOBAL;

Display the partitioning method of the table.

SELECT owner, table_name, partitioning_type AS type, autolist, interval
FROM   dba_part_tables WHERE  owner='SH' ; 

OWNER  TABLE_NAME      TYPE  AUTOLIST INTERVAL
------ --------------- ----- -------- -------------------------
SH     SALES           RANGE NO

Display the partitions of the table and the high values in each partition from code2.

SELECT partition_name, high_value FROM dba_tab_partitions
WHERE  table_name = 'SALES' and table_owner='SH';

Display the indexes on the table and observe which indexes are partitioned.

SELECT index_name, index_type, partitioned FROM dba_indexes
WHERE  table_name='SALES';

INDEX_NAME                 INDEX_TYPE                  PAR
-------------------------- --------------------------- ---
SALES_PROD_BIX             BITMAP                      YES
SALES_CUST_BIX             BITMAP                      YES
SALES_TIME_BIX             BITMAP                      YES
SALES_CHANNEL_BIX          BITMAP                      YES
I2_PROMO_ID                NORMAL                      NO

Display the type of partitioning of the partitioned indexes.

SELECT index_name, locality, partitioning_type AS type, autolist, interval 
FROM   dba_part_indexes WHERE owner='SH';

INDEX_NAME                 LOCALI TYPE  AUTOLIST INTERVAL
-------------------------- ------ ----- -------- -------------------------
SALES_CHANNEL_BIX          LOCAL  RANGE NO
SALES_CUST_BIX             LOCAL  RANGE NO
SALES_PROD_BIX             LOCAL  RANGE NO
SALES_TIME_BIX             LOCAL  RANGE NO

In another session, Session2, log in to PDB1 as SH to increase the quantity sold in SALES_Q1_2000 partition.
```
CONNECT sh@PDB1
Enter password: password
```

Increase the quantity sold in SALES_Q1_2000 partition.

UPDATE sh.sales PARTITION (sales_q1_2000) SET QUANTITY_SOLD=QUANTITY_SOLD*10; 

62197 rows updated.

In Session1, merge the partition of the year 2000 to a single partition. This operation can be done at the same time as the DML operation.
```
ALTER TABLE sh.sales
        MERGE PARTITIONS sales_q1_2000,sales_q2_2000,sales_q3_2000,sales_q4_2000
        INTO PARTITION sales_2000
        COMPRESS UPDATE INDEXES ONLINE; 
```
The statement waits for the UPDATE in the second session to complete.
In Session2, commit the UPDATE statement.
```
COMMIT;
```

In Session1, the ALTER TABLE statement completes.

ALTER TABLE sh.sales
      MERGE PARTITIONS sales_q1_2000, sales_q2_2000, sales_q3_2000, 
                       sales_q4_2000
       INTO PARTITION sales_2000
       COMPRESS UPDATE INDEXES ONLINE; 

Table altered.

Verify that the four partitions are merged into one single partition. The query of step 10 reports 28 partitions. The current query reports 25 partitions: see code3.
```
SELECT partition_name, high_value FROM dba_tab_partitions
WHERE  table_name = 'SALES' and table_owner = 'SH';
```
Optionally, drop the SH user.
```
DROP USER sh CASCADE;
```
Quit the session.
```
EXIT
```