主页 / dba / 问题 / 55081
Accepted
René Nyffenegger
Asked: 2013-12-16 23:32:56 +0800 CST

为什么使用 dbms_redefinition 后 SQL*Plus 的 desc 表不显示非空约束

  • 772

我有下表要重新定义：

create table tq84_redefinition (
  id number primary key,
  ts1 timestamp not null,
  ts2 timestamp
);

请注意not null列上的约束ts1。

使用dbms_redefinition，我专门用copy_constraints => true。

create table tq84_redefinition_int (
    id number,                            -- Note: no primary key to prevent «ORA-01408: such column list already indexed»
    ts1 date,
    ts2 date,
    duration_minutes as ((ts2 - ts1) * 24 * 60)
);


begin
  dbms_redefinition.start_redef_table(
    user, 'tq84_redefinition', 'tq84_redefinition_int',
   'id, '               ||
   'to_date(to_char(ts1, ''ddmmyyyyhh24miss''), ''ddmmyyyyhh24miss'') ts1, ' ||
   'to_date(to_char(ts2, ''ddmmyyyyhh24miss''), ''ddmmyyyyhh24miss'') ts2');
end;
/



-- set serveroutput on

declare  
  cnt_errors binary_integer;
begin

  dbms_redefinition.copy_table_dependents(
    user, 'tq84_redefinition', 'tq84_redefinition_int', 
    -------------------------------------------------------
    copy_indexes     => dbms_redefinition.cons_orig_params,
    copy_triggers    => true, 
    copy_constraints => true, 
    copy_privileges  => true, 
    ignore_errors    => false, 
    num_errors       => cnt_errors,
    copy_statistics  => true, 
    copy_mvlog       => false);

  if cnt_errors > 0 then
     dbms_output.put_line('There were ' || cnt_errors || ' errors.');
  end if;

end;
/

exec dbms_redefinition.sync_interim_table(user, 'tq84_redefinition', 'tq84_redefinition_int');
exec dbms_redefinition.finish_redef_table(user, 'tq84_redefinition', 'tq84_redefinition_int');

除了desc在 SQL*Plus 中没有正确显示not null约束之外，一切似乎都运行良好：

...> desc tq84_redefinition;
 Name                        Null?    Type
 --------------------------- -------- ---------------
 ID                                   NUMBER
 TS1                                  DATE
 TS2                                  DATE
 DURATION_MINUTES                     NUMBER

但是，在某处，非空约束已经到位。我可以通过发出

select constraint_type, constraint_name, search_condition
  from user_constraints
 where table_name = 'TQ84_REDEFINITION';

此外，如果我尝试插入记录 [ insert into tq84_redefinition (id) values (999999)]，我会收到（正确的）错误消息ORA-01400: cannot insert NULL into ("META"."TQ84_REDEFINITION"."TS1")。

编辑：版本（v$version）是：

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

编辑 2 @Munchi，您建议的 select 语句的结果

select 
  column_name as "Name",
  nullable as "Null?",
  concat(concat(concat(data_type,'('),data_length),')') as "Type"
from
  user_tab_columns
where
  table_name = 'TQ84_REDEFINITION'

是

Name                           N Type
------------------------------ - --------------
ID                             Y NUMBER(22)
TS1                            Y DATE(7)
TS2                            Y DATE(7)
DURATION_MINUTES               Y NUMBER(22)
oracle sqlplus

2 个回答

  1. Best Answer

    这是一个已知的错误（不幸的是，描述不公开）：

    错误：4396234 ET10.2OREDEF：*_TAB_COLUMNS 表的 NULLABLE COL 在线 REDEF 后未更新

    NOT NULL 约束被复制为 NOVALIDATE，您必须手动将它们设置为 VALIDATE 状态，例如：

    ALTER TABLE t84_redefenition ENABLE VALIDATE CONSTRAINT constraint_name;

    主键问题类似，但没有报告错误。但是我发现禁用和重新启用它可以解决问题。

    ALTER TABLE t84_redefinition DISABLE PRIMARY KEY;
ALTER TABLE t84_redefinition ENABLE PRIMARY KEY;
    • 11

  2. 表列定义的Not Null子句设置一个标志$sys$cols并在列上生成一个sys_xxxxxx约束。

    dbms_redef但是只是复制sys_xxxxxxx约束并省略设置标志。正是这个标志被解码为“Nullable？” 在表的 SQLDeveloper 列选项卡上显示（比如说）。

    那么这有什么效果——

    好吧，保持了参照完整性 - 尝试将 null 插入存在约束的列中仍然失败。

    但是，我在某处读到（我将不得不再次找到那篇文章）CBO 将$sys$cols标志（而不是约束）视为某些查询优化的一部分。

    考虑基于非空列的索引。该索引可能是满足select count(*)类型查询而不是进行 FTS 的候选。如果$sys$cols它本身为空（不是由 re-def 设置），则不会考虑该索引。

    我认为值得为您的重新定义代码添加步骤

    重新定义后 - 从约束中保存非空列的名称
    - 删除非空约束 - 通过alter table modify col not null子句重新应用

    甚至只是为了平息那些发现前后差异的 Java 开发者的抱怨。

    • 1

相关问题