Oracle by Example brandingMerging Partitions Online

section 0 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.

section 1Merge Partitions Online

  1. Log in to PDB1 PDB to create the SH user.
    sqlplus system@PDB1
    Enter password: password
  2. Create the SH user.
    DROP USER sh CASCADE;
    CREATE USER sh IDENTIFIED BY password;
  3. 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;
  4. Create the RANGE partitioned table SH.SALES from code1.
  5. Quit the session.
    EXIT
    
  6. 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.

  7. Log in to PDB1 as SYSTEM to create the SH.I2_PROMO_ID on the PROMO_ID column.
    sqlplus system@pdb1
    Enter password: password
  8. 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;
    
  9. 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
    
  10. 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'; 
    
  11. 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
    
  12. 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
    
  13. 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
  14. 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.
  15. 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.

  16. In Session2, commit the UPDATE statement.
    COMMIT;
    
  17. 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.
    
  18. 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';
    
  19. Optionally, drop the SH user.
    DROP USER sh CASCADE;
  20. Quit the session.
    EXIT