Skip to Main Content
  • Questions
  • Usage and limitations on PART_NAME parameter in DBMS_REDEFINITION APIs

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Narendra.

Asked: January 21, 2023 - 1:10 am UTC

Last updated: January 25, 2023 - 2:50 am UTC

Version: 19.16

You Asked

Hello Connor/Chris,

Hope you are doing well.
Recently, I came to know about the PART_NAME parameter for multiple APIs of DBMS_REDEFINITION package from the documentation. However, I could not find much about what kind of changes are permitted (and prevented) at partition level, either in the documentation or in any MOS notes.
The documentation states below
part_name
Name of the partition being redefined. If redefining only a single partition of a table, specify the partition name in this parameter. NULL implies the entire table is being redefined. Can take a comma-delimited list of partition names to be redefined.

This lead me to believe that I might be able to leverage this feature to perform a large update of a partitioned table column but with one partition at a time using DBMS_REDEFINITION. However, this does not appear to work and fails with error during START_REDEF_TABLE API call.
Would you be able to help in shedding more light on what is the purpose of PART_NAME parameter in DBMS_REDEFINITION APIs?

Below is my set up to reproduce the issue

DROP TABLE TBL_DATEPART PURGE;
DROP TABLE TBL_TSPART PURGE;

CREATE TABLE TBL_DATEPART 
(
  OBJECT_ID NUMBER NOT NULL 
, OWNER VARCHAR2(50) NOT NULL 
, OBJECT_TYPE VARCHAR2(50) NOT NULL 
, OBJECT_NAME VARCHAR2(200) NOT NULL 
, CREATED DATE NOT NULL 
, LAST_DDL_TIME DATE NOT NULL 
, CONSTRAINT TBL_DATEPART_PK PRIMARY KEY(OBJECT_ID) ENABLE 
) 
PARTITION BY RANGE (LAST_DDL_TIME) 
(
  PARTITION Y2019 VALUES LESS THAN (TO_DATE('01-JAN-2020','DD-MON-YYYY'))  
, PARTITION Y2020 VALUES LESS THAN (TO_DATE('01-JAN-2021','DD-MON-YYYY'))  
, PARTITION Y2021 VALUES LESS THAN (TO_DATE('01-JAN-2022','DD-MON-YYYY'))  
, PARTITION YMAX VALUES LESS THAN (MAXVALUE)  
);

insert into TBL_DATEPART (object_id, owner, object_type, object_name, created, last_ddl_time)
select object_id, owner, object_type, object_name, created, last_ddl_time
from dba_objects
where last_ddl_time is not null ;

commit; 

ALTER TABLE TBL_DATEPART ADD last_ddl_ts TIMESTAMP ;

CREATE TABLE TBL_TSPART 
(
  OBJECT_ID NUMBER 
, OWNER VARCHAR2(50) 
, OBJECT_TYPE VARCHAR2(50) 
, OBJECT_NAME VARCHAR2(200) 
, CREATED DATE
, LAST_DDL_TIME DATE
, LAST_DDL_TS TIMESTAMP
) 
PARTITION BY RANGE (LAST_DDL_TIME) 
(
  PARTITION Y2019 VALUES LESS THAN (TO_TIMESTAMP('01-JAN-2020','DD-MON-YYYY'))  
, PARTITION Y2020 VALUES LESS THAN (TO_TIMESTAMP('01-JAN-2021','DD-MON-YYYY'))  
, PARTITION Y2021 VALUES LESS THAN (TO_TIMESTAMP('01-JAN-2022','DD-MON-YYYY'))  
, PARTITION YMAX VALUES LESS THAN (MAXVALUE)  
);

BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE(uname => 'NP', tname => 'TBL_DATEPART', options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

BEGIN 
-- Below fails. My objective is to be able to update the value of newly added column based on an existing column.  
DBMS_REDEFINITION.START_REDEF_TABLE(uname => 'NP', orig_table => 'TBL_DATEPART', int_table => 'TBL_TSPART', col_mapping => 'OBJECT_ID OBJECT_ID, OWNER OWNER, OBJECT_TYPE OBJECT_TYPE, OBJECT_NAME OBJECT_NAME, CREATED CREATED, LAST_DDL_TIME LAST_DDL_TIME, to_timestamp(LAST_DDL_TIME) LAST_DDL_TS', options_flag => dbms_redefinition.cons_use_rowid, part_name => 'Y2019');
-- Below works where I do not specify PART_NAME parameter.
-- DBMS_REDEFINITION.START_REDEF_TABLE(uname => 'NP', orig_table => 'TBL_DATEPART', int_table => 'TBL_TSPART', col_mapping => 'OBJECT_ID OBJECT_ID, OWNER OWNER, OBJECT_TYPE OBJECT_TYPE, OBJECT_NAME OBJECT_NAME, CREATED CREATED, LAST_DDL_TIME LAST_DDL_TIME, to_timestamp(LAST_DDL_TIME) LAST_DDL_TS', options_flag => dbms_redefinition.cons_use_rowid);
end; 
/

ORA-42000: invalid online redefinition column mapping for table "NP"."TBL_DATEPART"
ORA-06512: at "SYS.DBMS_REDEFINITION", line 113
ORA-06512: at "SYS.DBMS_REDEFINITION", line 4363
ORA-06512: at "SYS.DBMS_REDEFINITION", line 5753
ORA-06512: at line 3
42000. 0000 -  "invalid online redefinition column mapping for table \"%s\".\"%s\""
*Document: YES
*Cause:    An attempt was made to change the logical structure of a
           materialized view log, queue table, or a partition
           by specifying a non-NULL column mapping during online
           redefinition.
*Action:   Pass a NULL column mapping parameter.

and Connor said...

PART_NAME is going to be mutually exclusive with *structural changes* because you can't have a table with some partitions with structure "A" and some with structure "B".

PART_NAME pre-dates some of the things that you can now do online from 12.2 onwards, so it was used for things like compressing / moving - re-sorting on a partition by partition basis without service interruption

I expect you're referring to the post by Tim about migrating billions of rows to a new partition key. I also had a look at PART_NAME as a means to do this incrementally, but I think we're out of luck

Hope that helps.

Rating

  (1 rating)

Comments

Thanks

Narendra, January 24, 2023 - 11:26 pm UTC

Hello Connor,

Thank you for taking time to answer my question.
You are right that DBMS_REDEFINITION can/should not support structural changes to individual partitions. Also, I agree that with 19c, we have so many options to modify table structure online that there is probably not a strong enough case to ask for DBMS_REDEFINITION to be able to update a column on per partition basis but it would be a really cool feature to have if it was indeed possible to use DBMS_REDEFINITION incrementally for implementing data manipulation online. If, for example, in my example above, the original table had been HASH PARTITIONED on OBJECT_ID instead of being RANGE PARTITIONED on LAST_DDL_TIME, it would mean that I do not have any historical/read-only partitions. With a really large table, if want to add a column and update its value in ONLINE manner, I am not sure it is possible, right?
Connor McDonald
January 25, 2023 - 2:50 am UTC

I am not sure it is possible, right?

That's what I'm currently researching

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database