Re: not null column during CTAS(not null definition did not come across)

thomas, thnx for the reply.

1. yes I can reproduce in three other tables.
2. the original table has subgroup_type_id nullable and it was altered to not null and include in part of primary key.
3. under the above described conditions I can not reproduce this bug in a test table within the same instance.

thnx ted    

Thomas Kyte <tkyte_at_oracle.com> wrote in message news:<a635n20dqf_at_drn.newsguy.com>...
> In article <44a19320.0203051045.47eb2c46_at_posting.google.com>, tedchyn_at_yahoo.com
> says...
> >
> >all,
> >
> >when creating a table using CTAS, I found not null definition on one of the
> >column did not propagate(see below). My question:
> >1. why some of not null column propagate whereas other column does not ?
> >
>
> must be more then meets the eye. In
>
> 716, 734, 806, 815, 817, 90
>
> I find:
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table contacts
> 2 (
> 3 ACCOUNT_ID NUMBER(10) NOT NULL,
> 4 ID NUMBER(22) NOT NULL,
> 5 CLIENT_DATA VARCHAR2(20),
> 6 NODE_START_DATE DATE,
> 7 NODE_END_DATE DATE,
> 8 ORIGIN_ORDER_ID VARCHAR2(14),
> 9 CONTACT_TYPE VARCHAR2(50),
> 10 NAME VARCHAR2(25),
> 11 CITY VARCHAR2(25),
> 12 TELEPHONE VARCHAR2(17),
> 13 EMAIL_ADDR VARCHAR2(17),
> 14 SUBGROUP_TYPE_ID NUMBER(10) NOT NULL
> 15 )
> 16 /
>
> Table created.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table contacts_new
> 2 tablespace users pctfree 0 storage
> 3 (initial 1m next 1m pctincrease 0) as select * from
> 4 contacts where 1=2;
>
> Table created.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> desc contacts;
> Name Null? Type
> ----------------------------------- -------- ------------------------
> ACCOUNT_ID NOT NULL NUMBER(10)
> ID NOT NULL NUMBER(22)
> CLIENT_DATA VARCHAR2(20)
> NODE_START_DATE DATE
> NODE_END_DATE DATE
> ORIGIN_ORDER_ID VARCHAR2(14)
> CONTACT_TYPE VARCHAR2(50)
> NAME VARCHAR2(25)
> CITY VARCHAR2(25)
> TELEPHONE VARCHAR2(17)
> EMAIL_ADDR VARCHAR2(17)
> SUBGROUP_TYPE_ID NOT NULL NUMBER(10)
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> desc contacts_new;
> Name Null? Type
> ----------------------------------- -------- ------------------------
> ACCOUNT_ID NOT NULL NUMBER(10)
> ID NOT NULL NUMBER(22)
> CLIENT_DATA VARCHAR2(20)
> NODE_START_DATE DATE
> NODE_END_DATE DATE
> ORIGIN_ORDER_ID VARCHAR2(14)
> CONTACT_TYPE VARCHAR2(50)
> NAME VARCHAR2(25)
> CITY VARCHAR2(25)
> TELEPHONE VARCHAR2(17)
> EMAIL_ADDR VARCHAR2(17)
> SUBGROUP_TYPE_ID NOT NULL NUMBER(10)
>
> happens consistently. Whats the original create table look like for contacts,
> can you reproduce this with a standalone testcase?
>
> >
> >SQL> desc sda_mcr.contacts;
> > Name Null? Type
> > ------------------------------- -------- ----
> > ACCOUNT_ID NOT NULL NUMBER(10)
> > ID NOT NULL NUMBER(22)
> > CLIENT_DATA VARCHAR2(20)
> > NODE_START_DATE DATE
> > NODE_END_DATE DATE
> > ORIGIN_ORDER_ID VARCHAR2(14)
> > CONTACT_TYPE VARCHAR2(50)
> > NAME VARCHAR2(25)
> > CITY VARCHAR2(25)
> > TELEPHONE VARCHAR2(17)
> > EMAIL_ADDR VARCHAR2(17)
> > SUBGROUP_TYPE_ID NOT NULL NUMBER(10)
> >
> >
> >SQL> create table SDA_MCR_ARCH.CONTACTS_new nologging
> > 2 tablespace MCR_ARCH_MD_DATA_1 pctfree 0 storage
> > 3 (initial 1m next 1m pctincrease 0) as select * from
> > 4 sda_mcr.contacts where 1=2;
> >
> >Table created.
> >
> >SQL> desc sda_mcr_arch.contacts_new;
> > Name Null? Type
> > ------------------------------- -------- ----
> > ACCOUNT_ID NOT NULL NUMBER(10)
> > ID NOT NULL NUMBER(22)
> > CLIENT_DATA VARCHAR2(20)
> > NODE_START_DATE DATE
> > NODE_END_DATE DATE
> > ORIGIN_ORDER_ID VARCHAR2(14)
> > CONTACT_TYPE VARCHAR2(50)
> > NAME VARCHAR2(25)
> > CITY VARCHAR2(25)
> > TELEPHONE VARCHAR2(17)
> > EMAIL_ADDR VARCHAR2(17)
> > SUBGROUP_TYPE_ID NUMBER(10) # not null missing
Received on Tue Mar 05 2002 - 19:17:23 CST