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
withSH.SALES
data to import intoSH.SALES
partitioned table. Download exp_sh_pdb1.dmp to thelabs
directory created on your server/home/oracle/labs.
- You will use two independent console sessions in this OBE.
Merge
Partitions Online
- Log in to
PDB1
PDB to create theSH
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 theDP_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 tableSH.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 onALTER TABLE SALES ... FOREIGN KEY.
- Log in to
PDB1
asSYSTEM
to create theSH.I2_PROMO_ID
on thePROMO_ID
column.sqlplus system@pdb1 Enter password: password
- Create the
SH.I2_PROMO_ID
on thePROMO_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
asSH
to increase the quantity sold inSALES_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