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