Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question
    
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 Dec 16 '13 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. –  René Nyffenegger Dec 16 '13 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 Dec 16 '13 at 9:32
    
And there is no issue as for as I know to alter the timestamp to date with alter table. –  Munchi Dec 16 '13 at 9:35
    
Why isn't ts1 defined as NOT NULL in the intermediate table? –  Phil Dec 16 '13 at 10:35
show 3 more comments

1 Answer 1

up vote 7 down vote accepted

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;
share|improve this answer
    
Just tried it & you're right - still happens on 12c too. Good spot. –  Phil Dec 16 '13 at 11:17
2  
Another one of those half-baked Oracle features. –  Colin 't Hart Dec 16 '13 at 12:48
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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