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]