Oracle 12.2 has introduced a number of new features that ease partitioning, the most-awaited perhaps is the ALTER TABLE MODIFY syntax to convert a non-partitioned table to partitioned.
What do you do if your database version is pre-12.2? Partitioning a non-partitioned table in pre-12.2 databases is trickier, not impossible. Try DBMS_REDEFINITION: here’s how.
Pre-12.2: ORA-14006 with ALTER TABLE MODIFY
First, a note to clear up a potentially confusing error that shows up if you attempt the version 12.2 syntax on an older database version.
Let’s try ALTER TABLE MODIFY on a non-partitioned table to make it partitioned, on Oracle version 12.1.
That’s the non-partitioned table ACCOUNTS:
SQL> CREATE TABLE accounts 2 ( id NUMBER NOT NULL 3 , account_number NUMBER 4 , customer_id NUMBER 5 , branch_id NUMBER 6 , region VARCHAR(2) 7 , status VARCHAR2(1), 8 PRIMARY KEY (id)); Table created.
Try introducing partitioning using ALTER TABLE MODIFY:
SQL> -- PARTITION BY LIST (status)
SQL> ALTER TABLE accounts MODIFY
2 PARTITION BY LIST (status)
3 ( PARTITION statusB VALUES ('B'),
4 PARTITION statusA VALUES ('A'),
5 PARTITION statusG VALUES ('G')
6 );
PARTITION BY LIST (status)
*
ERROR at line 2:
ORA-14006: invalid partition name
You get the ORA-14006 error, about which the documentation says:
ORA-14006: invalid partition name
Cause: a partition name of the form identifier is expected but not present.Action: enter an appropriate partition name.
Actually, there’s no way you can make the partition name appropriate enough to bypass this error in a pre-12.2 database. The ALTER TABLE MODIFY syntax for partitioning a non-partitioned table is simply not supported.
Partitioning using DBMS_REDEFINITION: Approach
Steps to introduce partitioning to a non-partitioned table (say, T1) using DBMS_REDEFINITION:
- Create an empty partitioned interim table T2 with the same structure as the original table T1
- Run dbms_redefinition.start_redef_table to initiate the redefinition process
- Run dbms_redefinition.copy_table_dependents to copy the dependent objects of the original table T1 onto the interim table T2
- Run dbms_redefinition.finish_redef_table to complete the redefinition process
- Drop the interim table T2
Partitioning using DBMS_REDEFINITION: Step-wise Execution
Here is the step-wise execution for partitioning our table ACCOUNTS using DBMS_REDEFINITION.
The table ACCOUNTS, before we start, is not partitioned:
SQL> -- BEFORE: Check if original table is partitioned SQL> select table_name 2 , partitioned 3 from user_tables 4 where table_name like 'ACCOUNTS'; TABLE_NAME PARTITIONED ------------- -------------- ACCOUNTS NO SQL> -- Check partitioning details SQL> select partition_name 2 , partition_position 3 from user_tab_partitions 4 where table_name = 'ACCOUNTS'; no rows selected
1. Create an empty partitioned interim table with the same structure as the original table
Our aim is to retain the structure of ACCOUNTS table as-is, with PARTITION BY LIST (status) added to it. So we create an interim table ACCOUNTS_TMP in the same schema as original table ACCOUNTS, looking like ACCOUNTS with partitioning.
SQL> -- Create interim table with partitioning
SQL> CREATE TABLE accounts_tmp
2 ( id NUMBER
3 , account_number NUMBER
4 , customer_id NUMBER
5 , branch_id NUMBER
6 , region VARCHAR(2)
7 , status VARCHAR2(1)
8 )
9 PARTITION BY LIST (status)
10 ( PARTITION statusB VALUES ('B'),
11 PARTITION statusA VALUES ('A'),
12 PARTITION statusG VALUES ('G')
13 );
Table created.
At this time, the interim table is empty.
SQL> -- When created, interim table is empty SQL> select * from accounts_tmp; no rows selected
Note: we need not create indexes, constraints, grants, triggers, etc. on the interim table, as dbms_redefinition.copy_table_dependents (step 3) will take care of the dependencies.
2. Run dbms_redefinition.start_redef_table to initiate the redefinition process
Begin the redefinition process by calling START_REDEF_TABLE, with these inputs:
- Schema name in which the redefinition is to be performed
- Original table name
- Interim table name
SQL> -- Initiate the redefinition process SQL> exec dbms_redefinition.start_redef_table(USER, 'ACCOUNTS', 'ACCOUNTS_TMP'); PL/SQL procedure successfully completed.
After START_REDEF_TABLE, the data of the original table gets copied over to the interim table.
SQL> -- After start_redef_data
SQL> -- Data of original table is copied over to interim table
SQL> select * from accounts_tmp;
ID ACCOUNT_NUMBER CUSTOMER_ID BRANCH_ID RE S
---------- -------------- ----------- ---------- -- -
2 112 31 505 US B
3 113 56 688 UK B
1 111 23 505 US G
3. Run dbms_redefinition.copy_table_dependents to copy the dependent objects of the original table onto the interim table
COPY_TABLE_DEPENDENTS clones the dependent objects (such as triggers, indexes, materialized view logs, grants, constraints) and statistics from the original table to the interim table.
SQL> -- Clone table dependents from original table
SQL> -- to interim table
SQL> declare
2 v_err NUMBER := 0;
3 begin
4 dbms_redefinition.copy_table_dependents
5 (USER, 'ACCOUNTS', 'ACCOUNTS_TMP',
6 copy_indexes => dbms_redefinition.cons_orig_params,
7 num_errors => v_err);
8 dbms_output.put_line('Error count: '|| v_err);
9 end;
10 /
Error count: 0
PL/SQL procedure successfully completed.
Check that no errors occurred during the cloning of the objects. This can be done by verifying that num_errors is 0.
If num_errors is not 0, fix the cause of the error and rerun COPY_TABLE_DEPENDENTS.
4. Run dbms_redefinition.finish_redef_table to complete the redefinition process
FINISH_REDEF_TABLE completes the redefinition process.
SQL> -- Finish the redefinition process SQL> exec dbms_redefinition.finish_redef_table(USER, 'ACCOUNTS', 'ACCOUNTS_TMP'); PL/SQL procedure successfully completed.
Check the structure of the original table after this — bingo! PARTITION BY LIST has been added to the table.
SQL> -- AFTER: Check if original table is partitioned SQL> select table_name 2 , partitioned 3 from user_tables 4 where table_name like 'ACCOUNTS'; TABLE_NAME PARTITIONED ------------- -------------- ACCOUNTS YES SQL> -- Check partitioning details SQL> select partition_name 2 , partition_position 3 from user_tab_partitions 4 where table_name = 'ACCOUNTS'; PARTITION_NAME PARTITION_POSITION -------------------- ------------------ STATUSB 1 STATUSA 2 STATUSG 3
SQL> -- Redefinition done: non-partitioned table converted to partitioned
SQL> select dbms_metadata.get_ddl( 'TABLE', 'ACCOUNTS')
2 from dual;
DBMS_METADATA.GET_DDL('TABLE','ACCOUNTS')
----------------------------------------------------------------------------
CREATE TABLE "HR"."ACCOUNTS"
( "ID" NUMBER CONSTRAINT "SYS_C0027391674" NOT NULL ENABLE NOVALIDATE,
"ACCOUNT_NUMBER" NUMBER,
"CUSTOMER_ID" NUMBER,
"BRANCH_ID" NUMBER,
"REGION" VARCHAR2(2),
"STATUS" VARCHAR2(1),
CONSTRAINT "SYS_C0027391675" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBLSP" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBLSP"
PARTITION BY LIST ("STATUS")
(PARTITION "STATUSB" VALUES ('B') SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBLSP" ,
PARTITION "STATUSA" VALUES ('A') SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBLSP" ,
PARTITION "STATUSG" VALUES ('G') SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBLSP" )
You will see that the NOT NULL constraints are marked NOVALIDATE. According to Doc ID 1089860.1, constraints are copied in NOVALIDATE mode in order to speed up the redefinition process.
SQL> select constraint_name 2 ,constraint_type 3 ,validated 4 from user_constraints 5 where table_name = 'ACCOUNTS'; CONSTRAINT_NAME C VALIDATED ---------------- - ------------- SYS_C0027391674 C NOT VALIDATED SYS_C0027391675 P VALIDATED
After redefinition, you can run ALERT TABLE ENABLE VALIDATE to enable the NOT NULL constraint. Here’s a dynamic SQL script to do it for multiple constraints in one go:
SQL> -- Script to enable constraints for VALIDATE SQL> begin 2 for uc in 3 (select table_name 4 ,constraint_name 5 from user_constraints 6 where table_name = 'ACCOUNTS' ) loop 7 execute immediate 'alter table ' 8 || uc.table_name 9 ||' enable validate constraint ' 10 || uc.constraint_name; 11 end loop; 12 end; 13 / PL/SQL procedure successfully completed.
SQL> -- After enabling constraints for VALIDATE SQL> select constraint_name 2 ,constraint_type 3 ,validated 4 from user_constraints 5 where table_name = 'ACCOUNTS'; CONSTRAINT_NAME C VALIDATED ---------------- - ------------- SYS_C0027391674 C VALIDATED SYS_C0027391675 P VALIDATED
5. Drop the interim table
Now that the original table has got the partitions as we wanted, the interim table can be dropped.
SQL> -- Drop the interim table SQL> drop table accounts_tmp; Table dropped.
In Closing
This article shows how a non-partitioned table can be converted to a partitioned table with a series of steps using DBMS_REDEFINITION, in Oracle versions before 12.2.
{ 4 comments… read them below or add one }
Very well explained. Thank you.
@Venky: Thank you for the feedback.
Really helped me. Than you very much.
Sorry for my typo. Forgot that the keypad ‘K’ is not working. I had to replace the keyboard for this one. Cheers!