Converting Partitions Online

Before You Begin

This 15-minute tutorial shows you how to convert the partitioning method of a partitioned table, online.

Background

Before Oracle Database 18c, you had to manually convert the partitioning method of a table. Now you can use a simple ALTER TABLE SQL statement to convert a partitioning method online. In Oracle Database 18c, you can change a partitioning method online. For example, you can now convert the HASH method to the RANGE method, or add or remove sub-partitioning to a partitioned table to reflect a new workload and for more manageability of data. Repartitioning a table can improve performance, such as changing the partitioning key to get more partition pruning. This avoids a big downtime during the conversion of large partitioned tables. The ALTER TABLE MODIFY statement supports a completely non-blocking DDL to repartition a table.

What Do You Need?

Oracle Database 18c installed
A container database (CDB) and a pluggable database (PDB)

Convert HASH To RANGE Partitioned Tables Online

Log in to PDB1 PDB ( Session1) to create the SH1 and HR1 users.
```
sqlplus system@PDB1
Enter password: password
```

Create the SH1 and HR1 users.

DROP USER sh1 CASCADE;
CREATE USER sh1 IDENTIFIED BY password;
DROP USER hr1 CASCADE;
CREATE USER hr1 IDENTIFIED BY password;

Grant the users the CREATE SESSION, CREATE TABLE, UNLIMITED TABLESPACE system privileges and read and write privilege on the DP_PDB1 logical directory.
```
GRANT create session, create table, unlimited tablespace TO sh1, hr1;
```
```
CREATE DIRECTORY dp_pdb1 as '/home/oracle/labs';
GRANT read , write ON DIRECTORY dp_pdb1 TO sh1, hr1;
```

Create the HR1.EMP HASH partitioned table.

CREATE TABLE hr1.emp (empno NUMBER NOT NULL, ename VARCHAR2(10),
                      job VARCHAR2(9), mgr NUMBER(4), sal NUMBER(7,2))
       PARTITION BY HASH (sal) PARTITIONS 4;

Insert rows into the HR1.EMP table.

INSERT INTO hr1.emp VALUES (100, 'Adam','Clerk',202,54320);
INSERT INTO hr1.emp VALUES (101, 'Joe','Admin',202,24321);
INSERT INTO hr1.emp VALUES (103, 'Peter','Admin',202,95432);
INSERT INTO hr1.emp VALUES (104, 'Scott','Clerk',202,44324);
INSERT INTO hr1.emp VALUES (105, 'Luis','Salesman',208,24325);
INSERT INTO hr1.emp VALUES (106,'John','Salesman',208,33326);
INSERT INTO hr1.emp VALUES (202, 'Miles','Manager',208,81000);
INSERT INTO hr1.emp VALUES (208, 'Kale','Manager',null,85000);
COMMIT;

Create three indexes on the partitioned table:

The local I1_SAL index on SAL column
The global unique I2_EMPNO index on EMPNO column
The global I3_MGR index on MGR column

CREATE INDEX hr1.i1_sal ON hr1.emp (sal) LOCAL;
CREATE UNIQUE INDEX hr1.i2_empno ON hr1.emp (empno);
CREATE INDEX hr1.i3_mgr ON hr1.emp (mgr);

Display the partitioning method of the table.

SELECT owner, table_name, partitioning_type AS type, 
       autolist, interval, interval_subpartition, 
       autolist_subpartition
FROM   dba_part_tables 
WHERE  owner='HR1';

OWNER  TABLE_NAME TYPE  AUTOLIST INTERVAL        INTERVAL_SUB AUTOLIST_SUB
------ ---------- ----- -------- --------------- ------------ ------------
HR1    EMP        HASH  NO                                    NO

Display the partitions of the table.

SELECT composite, partition_name, high_value 
FROM   dba_tab_partitions
WHERE  table_name = 'EMP' AND table_owner='HR1';

COM PARTITION_NAME HIGH_VALUE
--- -------------- --------------
NO  SYS_P248
NO  SYS_P249
NO  SYS_P250
NO  SYS_P251

Display the list of indexes and whether they are partitioned or not.

SELECT index_name, PARTITIONED 
FROM   dba_indexes WHERE index_name LIKE 'I%' and owner='HR1';

INDEX_NAME                 PAR
-------------------------- ---
I1_SAL                     YES
I3_MGR                     NO
I2_EMPNO                   NO

Display the type of partitioning of the partitioned indexes.

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

INDEX_NAME LOCALI TYPE  AUTOLIST INTERVAL INTERVAL_SUB AUTOLIST_SUB
---------- ------ ----- -------- -------- ------------ ------------
I1_SAL     LOCAL  HASH  NO                             NO

Display the partitions of the index.

SELECT index_name, partition_name, high_value
FROM   dba_ind_partitions
WHERE  index_name IN ('I1_SAL','I2_EMPNO','I3_MGR') AND index_owner = 'HR1';

Read the query result.

INDEX_NAME PARTITION_NAME HIGH_VALUE     
---------- -------------- -------------- 
I1_SAL     SYS_P248                     
I1_SAL     SYS_P249                      
I1_SAL     SYS_P250                      
I1_SAL     SYS_P251

In another terminal window, log in to PDB1 as HR1 to update the employees' salary. (Session2)
```
sqlplus hr1@PDB1
Enter password: password
```

Update the employees' salary.

UPDATE hr1.emp SET mgr=208 WHERE empno=100;

1 row updated.

In Session1, attempt to convert the HASH partitioned table to a RANGE partitioned table.

ALTER TABLE hr1.emp MODIFY
   PARTITION BY RANGE (empno) INTERVAL (100)
   (PARTITION p1 VALUES LESS THAN (200), PARTITION p2 VALUES LESS THAN (500))  
     UPDATE INDEXES 
      (hr1.i1_sal LOCAL, hr1.i2_empno GLOBAL PARTITION BY RANGE (empno) 
      (PARTITION ip1 VALUES LESS THAN (MAXVALUE)));
ALTER TABLE hr.emp MODIFY
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Since the ONLINE keyword is not added to the statement, the operation cannot get the exclusive lock. Re-execute the operation with the ONLINE keyword.

ALTER TABLE hr1.emp MODIFY
   PARTITION BY RANGE (empno) INTERVAL (100)
   (PARTITION p1 VALUES LESS THAN (200), PARTITION p2 VALUES LESS THAN (500)) 
ONLINE
   UPDATE INDEXES 
   (hr1.i1_sal LOCAL, hr1.i2_empno GLOBAL PARTITION BY RANGE (empno)
    (PARTITION ip1 VALUES LESS THAN (MAXVALUE)));

The statement waits for the UPDATE in the Session2 to complete. In Session2, commit the UPDATE statement.
```
COMMIT;
```

In Session1, previously entered

 ALTER
                    TABLE

statement completes.

ALTER TABLE hr1.emp MODIFY
   PARTITION BY RANGE (empno) INTERVAL (100)
   (PARTITION p1 VALUES LESS THAN (200), PARTITION p2 VALUES LESS THAN (500))   
ONLINE
    UPDATE INDEXES  
(hr1.i1_sal LOCAL, hr1.i2_empno GLOBAL PARTITION BY RANGE (empno)
      (PARTITION ip1 VALUES LESS THAN (MAXVALUE)));

Table altered.

Display the new partitioning method of the table.

SELECT owner, table_name, partitioning_type AS type, 
       autolist, interval, interval_subpartition, 
       autolist_subpartition
FROM   dba_part_tables WHERE  owner='HR1';

OWNER TABLE_NAME    TYPE  AUTOLIST INTERVAL INTERVAL_SUB AUTOLIST_SUB
----- ------------- ----- -------- -------- ------------ ------------
HR1   EMP           RANGE NO       100                   NO

Display the new partitions of the table.

SELECT partition_name, high_value FROM dba_tab_partitions
WHERE  table_name = 'EMP' and table_owner='HR1';

PARTITION_NAME HIGH_VALUE
-------------- --------------
P1             200
P2             500

Display the list of indexes and whether they are partitioned or not.

SELECT index_name, partitioned FROM dba_indexes 
WHERE  index_name LIKE 'I%' and owner='HR1';

INDEX_NAME PAR
---------- ---
I1_SAL     YES
I2_EMPNO   YES
I3_MGR     NO

Display the type of partitioning of the partitioned indexes.

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

INDEX_NAME LOCALI TYPE  AUTOLIST INTERVAL INTERVAL_SUB AUTOLIST_SUB
---------- ------ ----- -------- -------- ------------ ------------
I2_EMPNO   GLOBAL RANGE NO                             NO
I1_SAL     LOCAL  RANGE NO       100                   NO

Display the type of partitioning of the partitioned indexes.

SELECT index_name,partition_name, high_value 
FROM   dba_ind_partitions
WHERE  index_name IN ('I1_SAL','I2_EMPNO','I3_MGR') AND index_owner='HR1';

INDEX_NAME PARTITION_NAME HIGH_VALUE     
---------- -------------- -------------- 
I1_SAL     P1             200            
I1_SAL     P2             500            
I2_EMPNO   IP1            MAXVALUE

Convert LIST To LIST AUTOMATIC Partitioned Tables Online

Create the SH1.SALES_BY_REGION_AND_CHANNEL LIST partitioned table. The table is partitioned by LIST on two keys, STATE and CHANNEL.

CREATE TABLE sh1.sales_by_region_and_channel
    ( deptno number, deptname varchar2(20), quarterly_sales number(10, 2), 
      state varchar2(2), channel varchar2(1))
      PARTITION BY LIST (state, channel)
       (PARTITION q1_northwest_direct VALUES (('OR', 'D'), ('WA','D')),
        PARTITION q1_northwest_indirect VALUES (('OR', 'I'), ('WA','I')),
        PARTITION q1_southwest_direct VALUES (('AZ', 'D'), ('UT', 'D'), 
('NM','D')),
        PARTITION q1_ca_direct VALUES ('CA','D'),
        PARTITION rest VALUES (DEFAULT));

Insert rows into the SH1.SALES_BY_REGION_AND_CHANNEL table and commit. Use code1 to complete this operation.

Display the partitioning method of the table.

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

OWNER TABLE_NAME                  TYPE  AUTOLIST INTERVAL
----- --------------------------- ----- -------- --------
SH1   SALES_BY_REGION_AND_CHANNEL LIST  NO

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

SELECT partition_name, high_value FROM dba_tab_partitions
WHERE  table_name = 'SALES_BY_REGION_AND_CHANNEL';

Read the query result.

PARTITION_NAME        HIGH_VALUE
--------------------- --------------------------------------------
Q1_CA_DIRECT          ( 'CA', 'D' )
Q1_NORTHWEST_DIRECT   ( 'OR', 'D' ), ( 'WA', 'D' )
Q1_NORTHWEST_INDIRECT ( 'OR', 'I' ), ( 'WA', 'I' )
Q1_SOUTHWEST_DIRECT   ( 'AZ', 'D' ), ( 'UT', 'D' ), ( 'NM', 'D' )
REST                  DEFAULT

In Session2, connect to in PDB1 as SH1 to increase the quarterly sales.
```
CONNECT sh1@PDB1
Enter password: password
```

Increase the quarterly sales.

UPDATE sh1.sales_by_region_and_channel 
SET quarterly_sales=quarterly_sales*10;

7 rows updated.

In Session1, attempt to convert the LIST partitioned table on two keys to a LIST AUTOMATIC partitioned table on one key.

ALTER TABLE sh1.sales_by_region_and_channel MODIFY  
    PARTITION BY LIST (state) AUTOMATIC
     (PARTITION northwest VALUES ('OR', 'WA'),
      PARTITION southwest VALUES ('AZ', 'UT', 'NM'),
      PARTITION california VALUES ('CA'),
   PARTITION rest VALUES (DEFAULT)) ONLINE;   
*
ERROR at line 1:
ORA-14851: DEFAULT [sub]partition cannot be specified for AUTOLIST
[sub]partitioned objects.

Re-execute the operation without the DEFAULT partition.

ALTER TABLE sh1.sales_by_region_and_channel MODIFY  
    PARTITION BY LIST (state) AUTOMATIC
     (PARTITION northwest VALUES ('OR', 'WA'),
      PARTITION southwest VALUES ('AZ', 'UT', 'NM'),
      PARTITION california VALUES ('CA'))
	ONLINE UPDATE INDEXES;

The statement waits for the UPDATE in the Session2 to complete.

In Session2, commit the UPDATE statement.
```
COMMIT;
```

In Session1, the previously entered


                    ALTER TABLE

statement completes.

ALTER TABLE sh1.sales_by_region_and_channel MODIFY  
    PARTITION BY LIST (state) AUTOMATIC
     (PARTITION northwest VALUES ('OR', 'WA'),
      PARTITION southwest VALUES ('AZ', 'UT', 'NM'),
      PARTITION california VALUES ('CA'))
	ONLINE UPDATE INDEXES;

Table altered.

Display the new partitioning method of the table.

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

OWNER TABLE_NAME                  TYPE  AUTOLIST INTERVAL
----- --------------------------- ----- -------- --------
SH1   SALES_BY_REGION_AND_CHANNEL LIST  YES

Display the partitions of the table and the high values in each partition. Compare the result with code2.
```
SELECT partition_name, high_value FROM dba_tab_partitions
WHERE  table_name = 'SALES_BY_REGION_AND_CHANNEL';
```

Drop the table.

DROP TABLE sh1.sales_by_region_and_channel PURGE;

Convert LIST AUTOMATIC Partitioned To LIST Subpartitioned Tables Online

Create the HR1.T LIST AUTOMATIC partitioned table. The table is partitioned by LIST on one key,

C1.

CREATE TABLE hr1.t (c1 number, c2 number)
  PARTITION BY LIST (c1) AUTOMATIC
  (PARTITION p1 values (1),
   PARTITION p2 values (2),
   PARTITION p3 values (3));

Insert rows into HR1.T table and commit from code3.

Display the partitioning method of the table.

SELECT owner, table_name, partitioning_type AS type,
       autolist, subpartitioning_type
FROM   dba_part_tables WHERE table_name = 'T' ;  

OWNER TABLE_NAME    TYPE      AUT SUBPARTIT
----- ------------- --------- --- ---------
HR1   T             LIST      YES NONE

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

SELECT partition_name, high_value FROM dba_tab_partitions
WHERE  table_name = 'T';  

PARTITION_NAME HIGH_VALUE
-------------- --------------
P1             1
P2             2
P3             3

In Session2, log in to PDB1 as HR1 to increase the values in C2 column.
```
CONNECT hr1@PDB1
Enter password: password
```

Increase the values in C2 column.

UPDATE hr1.t SET c2=c2*10;

11 rows updated.

In Session1, convert the LIST AUTOMATIC partitioned table to a LIST AUTOMATIC subpartitioned table.

ALTER TABLE hr1.t MODIFY PARTITION BY LIST (c1) AUTOMATIC 
  SUBPARTITION BY list (c2)  SUBPARTITION TEMPLATE 
  ( SUBPARTITION sp1 VALUES (1), SUBPARTITION sp2 VALUES (2), 
    SUBPARTITION sp3 VALUES (3), SUBPARTITION sp_unknown VALUES (DEFAULT)) 
    (PARTITION p1 VALUES (1),  
     PARTITION p2 VALUES (2),
     PARTITION p3 VALUES (3))
   ONLINE;

The statement waits for the UPDATE in the second session to complete.

In Session2, commit the UPDATE statement.
```
COMMIT;
```

In Session1, the previously entered

 ALTER
                    TABLE

statement completes.

ALTER TABLE hr1.t MODIFY PARTITION BY LIST (c1) AUTOMATIC 
  SUBPARTITION BY list (c2)  SUBPARTITION TEMPLATE 
  ( SUBPARTITION sp1 VALUES (1), SUBPARTITION sp2 VALUES (2), 
    SUBPARTITION sp3 VALUES (3), SUBPARTITION sp_unknown VALUES (DEFAULT)) 
    (PARTITION p1 VALUES (1),  
     PARTITION p2 VALUES (2),
     PARTITION p3 VALUES (3))
   ONLINE;
Table altered.

Still in Session1, insert rows into

 HR1.T.

INSERT INTO hr1.t VALUES (1,2);
INSERT INTO hr1.t VALUES (2,3);
INSERT INTO hr1.t VALUES (3,4);
INSERT INTO hr1.t VALUES (3,5);
COMMIT;

Still in Session1, display the new subpartitioning method of the table.

SELECT owner, table_name, partitioning_type AS type,
       autolist, subpartitioning_type
FROM   dba_part_tables WHERE table_name = 'T';  

OWNER TABLE_NAME    TYPE      AUT SUBPARTIT
----- ------------- --------- --- ---------
HR1   T             LIST      YES LIST

Display the partitions of the table and the high values in each partition and compare to code4.
```
SELECT partition_name, subpartition_name, high_value
FROM   dba_tab_subpartitions
WHERE  table_name = 'T'; 
```

Display values from the first subpartition.

SELECT * FROM hr1.t SUBPARTITION (P1_SP2);

        C1         C2
---------- ----------
         1          2

Display values of the second subpartition.

SELECT * FROM hr1.t SUBPARTITION (P2_SP3);

        C1         C2
---------- ----------
         2          3

Display values of the third subpartition.

SELECT * FROM hr1.t SUBPARTITION (P2_SP_UNKNOWN);

        C1         C2
---------- ----------
         2         10
         2         20
         2         20
         2         30

Display values of the forth subpartition.

SELECT * FROM hr1.t SUBPARTITION (P3_SP_UNKNOWN);

        C1         C2
---------- ----------
         3         10
         3         20
         3         20
         3          4
         3          5

Optionally, drop the users.

CONNECT system@PDB1
Enter password:  password

DROP USER sh1 CASCADE;

DROP USER hr1 CASCADE;

Quit the session.
```
EXIT
```