Home » SQL & PL/SQL » SQL & PL/SQL » column with primary key is shown as nullable=Y in user_tab_columns after online redefinition (oracle db 11.2.0.3 on linux)
column with primary key is shown as nullable=Y in user_tab_columns after online redefinition [message #579091] Thu, 07 March 2013 09:19 Go to next message
bet32978
Messages: 4
Registered: November 2009
Junior Member
I create two tables A and B as interim table, where A has a primary key on column ID.
The column ID of table A is shown as nullable=N in user_tab_columns.

Then I do a online redefinition A->B.
The "new" table A (which was table B before the redefinition) now also has a primary key on column ID, but it is shown as nullable=Y in user_tab_columns

To reproduce,
with a given table SAMPLE with about 8000 rows and the structure
CREATE TABLE sample
(
  ID          NUMBER,
  LOTNUMBER   VARCHAR2(10 CHAR)                 NOT NULL,
  SENSORTYPE  VARCHAR2(6 CHAR)                  NOT NULL,
  CREATED     DATE                              NOT NULL,
  LOCATIONID  NUMBER(2)                         NOT NULL
)


I do the following (to give it a sense: A is not partitioned whereas B is (or will be))

  create table A as select * from sample;
  alter table A add constraint pk_a primary key (id);

  create table B as select * from sample where id is null;


A select * from user_tab_columns where table_name = 'A' shows N for column_name ID in column nullable.

A    ID           NUMBER            22              N    1
A    LOTNUMBER    VARCHAR2          40              N    2
A    SENSORTYPE   VARCHAR2          24              N    3
A    CREATED      DATE              7               N    4
A    LOCATIONID   NUMBER            22    2    0    N    5


now there runs an online redefinition with a self written plsql prcedure

 dbms_redefinition.can_redef_table(uname=>schema_name,
                                        tname=>table_name);

  begin
    dbms_redefinition.start_redef_table(uname=>schema_name,
                                             orig_table=>table_name,
                                             int_table=>interim_table,
                                             orderby_cols=>orderby_cols,
                                             col_mapping=>col_mapping);

    dbms_redefinition.copy_table_dependents(uname=>schema_name,
                                                 orig_table=>table_name,
                                                 int_table=>interim_table,
                                                 ignore_errors=>true,
                                                 num_errors=>num_err);
    dbms_output.put_line(num_err);

    dbms_redefinition.finish_redef_table(uname=>schema_name,
                                             orig_table=>table_name,
                                             int_table=>interim_table);
  exception
    when others then
      dbms_output.put_line (sqlerrm);
      dbms_redefinition.abort_redef_table(uname=>schema_name,
                                               orig_table=>table_name,
                                               int_table=>interim_table);
  end;


After that, the "new" table A has a primary key on ID but the column is shown as nullable:

select * from user_tab_columns where table_name = 'A';
A    ID           NUMBER            22              Y    1
A    LOTNUMBER    VARCHAR2          40              N    2
A    SENSORTYPE   VARCHAR2          24              N    3
A    CREATED      DATE              7               N    4
A    LOCATIONID   NUMBER            22    2    0    N    5


The primary key on A is listed in user_constraints and enabled.

is that a bug or am I missing something?

[Updated on: Thu, 07 March 2013 09:20]

Report message to a moderator

Re: column with primary key is shown as nullable=Y in user_tab_columns after online redefinition [message #579098 is a reply to message #579091] Thu, 07 March 2013 10:03 Go to previous messageGo to next message
Michel Cadot
Messages: 58927
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please Use SQL*Plus and copy and paste your session, the WHOLE session so that we can reproduce the exact same thing in different versions (including yours, of course).

Regards
Michel

[Updated on: Thu, 07 March 2013 10:04]

Report message to a moderator

Re: column with primary key is shown as nullable=Y in user_tab_columns after online redefinition [message #580396 is a reply to message #579091] Sat, 23 March 2013 23:35 Go to previous message
Barbara Boehmer
Messages: 7973
Registered: November 2002
Location: California, USA
Senior Member
The bug is that the constraint, although enabled, is in NO VALIDATE status, such that new rows will be validated, but old rows will not. So, you need to manually alter the constraint to VALIDATE. You can do that for all such constraints dynamically. Please see the simplified demonstration below.

SCOTT@orcl_11gR2> CREATE TABLE sample
  2  (
  3    ID	   NUMBER
  4  )
  5  /

Table created.

SCOTT@orcl_11gR2> create table A as select * from sample
  2  /

Table created.

SCOTT@orcl_11gR2> alter table A add constraint pk_a primary key (id)
  2  /

Table altered.

SCOTT@orcl_11gR2> create table B as select * from sample where id is null
  2  /

Table created.

SCOTT@orcl_11gR2> select column_name, nullable from user_tab_columns where table_name = 'A'
  2  /

COLUMN_NAME                    N
------------------------------ -
ID                             N

1 row selected.

SCOTT@orcl_11gR2> begin
  2    dbms_redefinition.can_redef_table(uname=>user,
  3  					     tname=>'A');
  4  end;
  5  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> declare
  2  	 num_err  pls_integer;
  3  begin
  4  	 dbms_redefinition.start_redef_table(uname=>user,
  5  						  orig_table=>'A',
  6  						  int_table=>'B');
  7  
  8  	 dbms_redefinition.copy_table_dependents(uname=>user,
  9  						      orig_table=>'A',
 10  						      int_table=>'B',
 11  						      ignore_errors=>TRUE,
 12  						      num_errors=>num_err);
 13  
 14  	 dbms_output.put_line(num_err);
 15  
 16  	 dbms_redefinition.finish_redef_table(uname=>user,
 17  						  orig_table=>'A',
 18  						  int_table=>'B');
 19    exception
 20  	 when others then
 21  	   dbms_output.put_line (sqlerrm);
 22  	   dbms_redefinition.abort_redef_table(uname=>user,
 23  						    orig_table=>'A',
 24  						    int_table=>'B');
 25    end;
 26  /
0

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> select column_name, nullable from user_tab_columns where table_name = 'A'
  2  /

COLUMN_NAME                    N
------------------------------ -
ID                             Y

1 row selected.

SCOTT@orcl_11gR2> select constraint_name, validated from user_constraints where table_name = 'A'
  2  /

CONSTRAINT_NAME                VALIDATED
------------------------------ -------------
PK_A                           NOT VALIDATED

1 row selected.

SCOTT@orcl_11gR2> insert into a values (null)
  2  /
insert into a values (null)
                      *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."A"."ID")


SCOTT@orcl_11gR2> begin
  2    for r in
  3  	 (select constraint_name
  4  	  from	 user_constraints
  5  	  where  table_name = 'A'
  6  	  and	 validated = 'NOT VALIDATED')
  7    loop
  8  	 execute immediate 'alter table a modify constraint '
  9  	 || r.constraint_name || ' validate';
 10    end loop;
 11  end;
 12  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> select column_name, nullable from user_tab_columns where table_name = 'A'
  2  /

COLUMN_NAME                    N
------------------------------ -
ID                             N

1 row selected.

SCOTT@orcl_11gR2> select constraint_name, validated from user_constraints where table_name = 'A'
  2  /

CONSTRAINT_NAME                VALIDATED
------------------------------ -------------
PK_A                           VALIDATED

1 row selected.

SCOTT@orcl_11gR2> insert into a values (null)
  2  /
insert into a values (null)
                      *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."A"."ID")


SCOTT@orcl_11gR2>

Previous Topic: Get count on group and pct of total count for each group
Next Topic: Using Variable inside a view
Goto Forum:
  


Current Time: Thu Aug 28 08:22:09 CDT 2014

Total time taken to generate the page: 0.15446 seconds