Oracle中执行DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS后如何查看错误详情

操作流程与初始问题 #

我采用创建中间表+DBMS_REDEFINITION API的方式，为Oracle现有表添加分区，初始操作步骤如下：

首先创建空的中间表：

CREATE TABLE my_schema.interm_tb AS SELECT * FROM my_schema.original_tb WHERE 1 = 0;

-- 为中间表添加分区

随后执行重定义脚本，输出显示num_errors > 0，但以SYS用户执行select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;时，无法查询到具体错误详情。执行的脚本如下：

DECLARE
         num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.start_redef_table('my_schema', 'original_tb', 'interm_tb');
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('my_schema', 'original_tb', 'interm_tb',
                                                        DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE,
                                                        num_errors);
  DBMS_OUTPUT.put_line('num_errors=' || num_errors);
END;

错误排查与解决步骤 #

查看具体错误信息 #

将COPY_TABLE_DEPENDENTS中的ignore_errors参数从TRUE改为FALSE，重新执行脚本后，即可直接抛出具体错误信息，无需查询视图。

错误原因与修复方案 #

修改参数后得到如下错误：

DECLARE
*
ERROR at line 1:
ORA-01442: column to be modified to NOT NULL is already NOT NULL
ORA-06512: at "SYS.DBMS_REDEFINITION", line 2304

经分析，中间表通过CREATE TABLE ... AS SELECT语句创建时，已经继承了原表的非空约束，后续调用COPY_TABLE_DEPENDENTS复制依赖项时，会尝试重复添加这些约束，导致冲突。

解决方法：在调用DBMS_REDEFINITION.start_redef_table之前，先删除中间表的所有约束，执行以下代码：

FOR q IN (
                    select 'alter table "' || owner || '"."' || table_name || '" DROP CONSTRAINT "' || CONSTRAINT_NAME || '"' as sql_text
                    from dba_constraints where table_name='interm_tb'
                    )
                    LOOP
                        DBMS_OUTPUT.put_line(q.sql_text);
                        EXECUTE IMMEDIATE q.sql_text;
                END LOOP;

执行完成后，再运行重定义脚本即可解决该错误。

内容的提问来源于stack exchange，提问作者wi2ard