Kamran Agayev's Oracle Blog

Oracle Certified Master

Beware of default values when using CTAS

Posted by Kamran Agayev A. on January 13th, 2011

These days I’m working on implementing a Sanitization (or Data Masking) on some critical tables which have million of rows. Instead of UPDATE command, I preffered using CTAS (Create table as ..) and forgot that CTAS doesn’t take default values, so added it as a seperate command in the sanitization script after getting call from application developers :)  Here’s a little demo:

[sourcecode]SQL> conn usr/usr
Connected.
SQL> create table t1 (id number default (0) not null);

Table created.

SQL> set long 10000

SQL> select dbms_metadata.get_ddl(‘TABLE’,’T1′,’USR’) from dual;

DBMS_METADATA.GET_DDL(‘TABLE’,’T1′,’USR’)
——————————————————————————–

  CREATE TABLE "USR"."T1"
   (    "ID" NUMBER DEFAULT (0) NOT NULL ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
SQL> create table t2 as select * from t1;

Table created.

SQL> select dbms_metadata.get_ddl(‘TABLE’,’T2′,’USR’) from dual;

DBMS_METADATA.GET_DDL(‘TABLE’,’T2′,’USR’)
——————————————————————————–

  CREATE TABLE "USR"."T2"
   (    "ID" NUMBER NOT NULL ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
SQL>[/sourcecode]

As you see, in the first result I got DEFAULT (0) when using GET_DDL command. However if table is created using CTAS, it doesn’t contain default value as it’s seen in the last query. You need to use ALTER TABLE and set default value for that column as follows:

[sourcecode]SQL> alter table t2 modify (id number default (0));

Table altered.

SQL> select dbms_metadata.get_ddl(‘TABLE’,’T2′,’USR’) from dual;

DBMS_METADATA.GET_DDL(‘TABLE’,’T2′,’USR’)
——————————————————————————–

  CREATE TABLE "USR"."T2"
   (    "ID" NUMBER DEFAULT (0) NOT NULL ENABLE

SQL>[/sourcecode]

2 Responses to “Beware of default values when using CTAS”

  1. Laurent Schneider Says:

    In one command…

    create table t1 (id number default (0) not null);
    create table t2(id default 1) as select * from t1;

  2. Kamran Agayev A. Says:

    Yes, that’s also nice option, but I forgot to mention the “default value” implicitly in CTAS command thinking that it will be taken automatically by CTAS

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>