Ora-32422 On START_REDEF_TABLE in 11.2.0.3 (Doc ID 1424215.1)

Last updated on AUGUST 15, 2013

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Information in this document applies to any platform.

Symptoms

Redefining a table fails with ORA-32422. The following testcase can be used to reproduce the behavior:

-- cleanup from previous run
exec dbms_redefinition.abort_redef_table(user, 'test2', 'tmp2', part_name => 'p1')
drop table test2;
drop table tmp2;

-- setup
create table test2 (
part_id number(9) not null
,nr number(9) not null
) partition by list (part_id) (
partition p1 values (1)
,partition p2 values (2)
);

create unique index test2#p on test2(part_id, nr) compress 1 local;
alter table test2 add constraint test2#p primary key(part_id, nr) using index test2#p;
insert into test2
select mod(object_id, 2) + 1, object_id
from all_objects
where rownum <= 5000;
commit;

-- run
exec dbms_redefinition.can_redef_table(user, 'test2', dbms_redefinition.cons_use_pk)
declare
l_num_err pls_integer;
begin
for i in 1..2 loop
execute immediate 'create table tmp2 as select * from test2 where 0=1';
-- copy_table_dependents doesn't work for partitioned tables, so let's do it manually
-- dbms_redefinition.copy_table_dependents(user, 'test2', 'tmp2', num_errors => l_num_err); exit when l_num_err != 0;
execute immediate 'create unique index tmp2#p on tmp2(part_id, nr) compress 1';
execute immediate 'alter table tmp2 add constraint tmp2#p primary key(part_id, nr) using index tmp2#p';
dbms_redefinition.start_redef_table(user, 'test2', 'tmp2', part_name => 'p' || i);
dbms_redefinition.finish_redef_table(user, 'test2', 'tmp2', part_name => 'p' || i);
execute immediate 'drop table tmp2';
end loop;
end;
/

The result for such testcase is:

declare
*
ERROR at line 1:
ORA-32422: commit SCN-based materialized view log cannot be created on table "K"."TEST2" when there exist direct load/PMOP log entries for the table
ORA-06512: at "SYS.DBMS_REDEFINITION", line 56
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1490
ORA-06512: at line 10

Changes

11.2.0.3 Online Redefinition uses Commit-SCN logs when possible for substantial performance improvements, however Commit-SCN logs are more sensitive to orphan entries in some meta data tables.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms