I have a table with partitions and I would like to find the most efficient way to empty a clob column for an entire partition.
I thought I could use DBMS_REDEFINITION with col_mapping and part_name but I am always getting ORA-42000.
Here are the statements I am using to reproduce the issue.
create table tkvav_part_redefinition (
id number primary key,
num varchar2(10),
ts timestamp,
mynum number,
mylob clob
);
insert into tkvav_part_redefinition values (1, '42' , systimestamp + 59/23,12,'123');
insert into tkvav_part_redefinition values (2, '-9.876', systimestamp + 51/31,34,'234');
insert into tkvav_part_redefinition values (3, '1.2e3' , systimestamp + 61/17,25,'345');
insert into tkvav_part_redefinition values (4, '42' , systimestamp -10 + 59/23,68,'123');
insert into tkvav_part_redefinition values (5, '-9.876', systimestamp -10 + 51/31,69,'234');
insert into tkvav_part_redefinition values (6, '1.2e3' , systimestamp -10 + 61/17,70,'345');
insert into tkvav_part_redefinition values (7, '42' , systimestamp -20 + 59/23,75,'123');
insert into tkvav_part_redefinition values (8, '-9.876', systimestamp -20 + 51/31,76,'234');
insert into tkvav_part_redefinition values (9, '1.2e3' , systimestamp -20 + 61/17,77,'345');
commit;
select rowid,x.* from tkvav_part_redefinition x;
ALTER TABLE tkvav_part_redefinition MODIFY
partition by range (ts) interval (NUMTODSINTERVAL(7, 'DAY'))
( PARTITION P1 VALUES LESS THAN (to_date('20260202', 'yyyymmdd'))
) ONLINE;
select TABLE_NAME, PARTITION_NAME from user_tab_partitions where table_name = 'TKVAV_PART_REDEFINITION';
create table tkvav_part_redefinition_int4 FOR EXCHANGE WITH TABLE tkvav_part_redefinition;
begin
dbms_redefinition.start_redef_table(
uname => user,
orig_table => 'tkvav_part_redefinition',
int_table => 'tkvav_part_redefinition_int4',
col_mapping => q'[
id,
num,
ts,
cast(null as number) mynum,
empty_clob() mylob
]',
options_flag => dbms_redefinition.cons_use_pk,
orderby_cols => null,
part_name => 'SYS_P1438977',
continue_after_errors => false,
copy_vpd_opt => dbms_redefinition.cons_vpd_none,
refresh_dep_mviews => 'N',
enable_rollback => false
);
end;
/ORA-42000: invalid online redefinition column mapping for table "EP2_ST675"."TKVAV_PART_REDEFINITION"
ORA-06512: at "SYS.DBMS_REDEFINITION", line 116
ORA-06512: at "SYS.DBMS_REDEFINITION", line 4441
ORA-06512: at "SYS.DBMS_REDEFINITION", line 5835
ORA-06512: at line 2
It works without any issues when I set part_name => null.
what am I doing wrong when using part_name ?
This is a documented restriction:
The column mapping string for START_REDEF_TABLE must be NULL. https://docs.oracle.com/en/database/oracle/oracle-database/26/admin/managing-tables.html#GUID-9A31C4B1-1DA5-4DF8-8567-8642821056D6 Which makes sense - the column mapping allows you to add/remove columns or change their data types. These are changes that have to apply to the whole table, not just a single partiton.
You've created the temp table FOR EXCHANGE. Is there a reason you're not using partition exchange?
You can set the columns by selecting NULL for these columns when you insert them into the temp table. Then flip them over:
alter table tkvav_part_redefinition_int4
add primary key ( id );
select *
from tkvav_part_redefinition partition for ( date'2026-04-04' );
ID NUM TS MYNUM MYLOB
---------- ---------- ----------------------------- ---------- --------------------------------------------------------------------------------
1 42 05/04/2026 02:21:52.000000000 12 123
2 -9.876 04/04/2026 04:16:59.000000000 34 234
insert into tkvav_part_redefinition_int4
select id,num,ts,
cast(null as number) mynum,
empty_clob() mylob
from tkvav_part_redefinition partition for ( date'2026-04-04' );
commit;
alter table tkvav_part_redefinition
exchange partition for ( date'2026-04-04' )
with table tkvav_part_redefinition_int4
update indexes;
select *
from tkvav_part_redefinition partition for ( date'2026-04-04' );
ID NUM TS MYNUM MYLOB
---------- ---------- ----------------------------- ---------- --------------------------------------------------------------------------------
1 42 05/04/2026 02:21:52.000000000 <null>
2 -9.876 04/04/2026 04:16:59.000000000 <null> If you need a more online process, dbms_redefinition has an execute_update procedure. As the docs say:
This procedure can optimize the performance of bulk updates to a table. Performance is optimized because the updates are not logged in the redo log.
The EXECUTE_UPDATE procedure automatically uses the components of online table redefinition, such an interim table, a materialized view, and a materialized view log, to enable optimized bulk updates to a table.So should come out faster than running the update normally:
select *
from tkvav_part_redefinition partition for ( date'2026-03-16' );
ID NUM TS MYNUM MYLOB
---------- ---------- ----------------------------- ---------- --------------------------------------------------------------------------------
7 42 16/03/2026 02:21:52.000000000 75 123
9 1.2e3 17/03/2026 02:55:01.000000000 77 345
begin
dbms_redefinition.execute_update ( q'!
update tkvav_part_redefinition
set mynum = null,
mylob = null
where ts >= date'2026-03-16'
and ts < date'2026-03-23'
!' );
end;
/
select *
from tkvav_part_redefinition partition for ( date'2026-03-16' );
ID NUM TS MYNUM MYLOB
---------- ---------- ----------------------------- ---------- --------------------------------------------------------------------------------
7 42 16/03/2026 02:21:52.000000000 <null> <null>
9 1.2e3 17/03/2026 02:55:01.000000000 <null> <null>