5

I have the following table that I want to redefine:

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

Please note the not null constraint on the column ts1.

Using dbms_redefinition, I specifically use 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');

Everything seems to work fine except that desc in SQL*Plus doesn't correctly display the not null constraint:

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

However, somewhere, the not null constraints is in place. I can see it by issuing a

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

Also, if I try to insert a record [insert into tq84_redefinition (id) values (999999)] I get the (correct) error message ORA-01400: cannot insert NULL into ("META"."TQ84_REDEFINITION"."TS1").

Edit: The version (v$version) is:

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

Edit 2 @Munchi, the result of your suggested select statement

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'

is

Name                           N Type
------------------------------ - --------------
ID                             Y NUMBER(22)
TS1                            Y DATE(7)
TS2                            Y DATE(7)
DURATION_MINUTES               Y NUMBER(22)
7
  • 1) What version are you on? I can't recreate it on 11.2. <br/> 2) Why don't you use alter table <table> modify (xx xx NOT NULL);
    – Munchi
    Commented Dec 16, 2013 at 8:22
  • The version is 11.2 (see my edit). I don't use alter table because I need to convert the timestamps to dates. Besides, I don't want to change the nullableness of the columns. Commented Dec 16, 2013 at 9:04
  • just wondering, what's the output of the following?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';
    – Munchi
    Commented Dec 16, 2013 at 9:32
  • And there is no issue as for as I know to alter the timestamp to date with alter table.
    – Munchi
    Commented Dec 16, 2013 at 9:35
  • Why isn't ts1 defined as NOT NULL in the intermediate table?
    – Philᵀᴹ
    Commented Dec 16, 2013 at 10:35

2 Answers 2

11

This is a known bug (description is not public unfortunately):

Bug:4396234 ET10.2OREDEF: NULLABLE COL OF *_TAB_COLUMNS TABLE NOT UPDATED AFTER ONLINE REDEF

The NOT NULL constraints are copied as NOVALIDATE, and you have to set them to VALIDATE state manually, e.g:

ALTER TABLE t84_redefenition ENABLE VALIDATE CONSTRAINT constraint_name;

The primary key issue is similar, but there is no bug reported for that. However I have found that disabling and reenabling it solves the problem.

ALTER TABLE t84_redefinition DISABLE PRIMARY KEY;
ALTER TABLE t84_redefinition ENABLE PRIMARY KEY;
2
  • Just tried it & you're right - still happens on 12c too. Good spot.
    – Philᵀᴹ
    Commented Dec 16, 2013 at 11:17
  • 3
    Another one of those half-baked Oracle features. Commented Dec 16, 2013 at 12:48
1

The Not Null clause of a table column definition sets a flag in $sys$cols and generates a sys_xxxxxx constraint on the column.

dbms_redef however just copies the sys_xxxxxxx constraint and omits setting the flag. It's this flag that gets decoded into the "Nullable?" display (say) on SQLDeveloper columns tab of a table.

So what's the effect of this -

Well, referential integrity is maintained - trying to insert a null into a column where the constraint exists still fails.

However I read somewhere (I'll have to find that article again) that the CBO looks at the $sys$cols flag (not the constraint) as part of optimisation for certain queries.

Consider an index based on a not null column. This index may be a candidate for satisfying a select count(*) type query rather than doing a FTS. If the $sys$cols is itself null (not set by re-def) that index won't be considered.

I think it is worth the effort of adding steps to your re-def code

After re-def - save the names of not null cols from constraints
- drop not null constraints - reapply via alter table modify col not null clause

Even just to quieten the Java dev moaners who spot the before and after difference.

2
  • The effect of the missing NOT NULL property on the optimizer is demonstrated by a Ask Tom Video by Connor: asktom.oracle.com/pls/asktom/…
    – eckes
    Commented Mar 1, 2018 at 3:31
  • 1
    You can run this select to see all the unfinished constraints (as soon as you did COPY_TABLE_DEPENDENTS): SELECT l.OWNER,l.TABLE_NAME,l.COLUMN_NAME,l.CONSTRAINT_NAME,s.STATUS,s.VALIDATED,s.SEARCH_CONDITION from USER_CONS_COLUMNS l,USER_CONSTRAINTS s where l.CONSTRAINT_NAME=s.CONSTRAINT_NAME and l.OWNER=s.OWNER and l.TABLE_NAME = s.TABLE_NAME and (s.STATUS!='ENABLED' or s.VALIDATED!='VALIDATED');
    – eckes
    Commented Mar 1, 2018 at 3:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.