Beware of Default Values Using CTAS

Beware of Default Values Using CTAS

Currently, I’m working on implementing a sanitization, also known as data masking, on some critical tables holding millions of rows. Instead of  using an UPDATE command, I prefer using Create Table AS (CTAS).  I forgot that CTAS does not take default values, so after getting a call from the application developers, I added it as a separate command in the sanitization script.

 Here’s a little demo:

01 SQL> conn usr/usr
02 Connected.
03 SQL> create table t1 (id number default (0) not null);
04   
05 Table created.
06   
07 SQL> set long 10000
08   
09 SQL> select dbms_metadata.get_ddl('TABLE','T1','USR') from dual;
10   
11 DBMS_METADATA.GET_DDL('TABLE','T1','USR')
12 ------------------------------------------------------------------------
13   
14   CREATE TABLE "USR"."T1"
15    (    "ID" NUMBER DEFAULT (0) NOT NULL ENABLE
16    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
17 SQL> create table t2 as select * from t1;
18   
19 Table created.
20   
21 SQL> select dbms_metadata.get_ddl('TABLE','T2','USR') from dual;
22   
23 DBMS_METADATA.GET_DDL('TABLE','T2','USR')
24 ------------------------------------------------------------------------
25   
26   CREATE TABLE "USR"."T2"
27    (    "ID" NUMBER NOT NULL ENABLE
28    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
29 SQL>

When using GET_DDL in the first case, the results show I got DEFAULT (0).  However, as in the second case, if the table is created using CTAS, it doesn’t contain default value as shown in the last query results. You need to use ALTER TABLE and set default value for that column as follows:

01 SQL> alter table t2 modify (id number default (0));
02   
03 Table altered.
04   
05 SQL> select dbms_metadata.get_ddl('TABLE','T2','USR') from dual;
06   
07 DBMS_METADATA.GET_DDL('TABLE','T2','USR')
08 ------------------------------------------------------------------------
09   
10   CREATE TABLE "USR"."T2"
11    (    "ID" NUMBER DEFAULT (0) NOT NULL ENABLE
12   
13 SQL>