Kamran Agayev’s Oracle Blog

Kamran Agayev’s Oracle Blog

Beware of default values when using CTAS

Posted by Kamran Agayev A. on January 13, 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:

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>

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:

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>

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

  1. In one command…

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

  2. 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

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: