how to set default value for column of new created table from select statement in 11g

[Deepthi Bittu]

I create a table in oracle 11g with the default value for one of the columns. Systax is "create table xyz(emp number,ename varchar2(100),s alary number default 0);". This created successfully. For some reasons I need to create another table with same old table structure and data. So i created a new table with name "abc" as "create table abc as select * from xyz". Here "abc" created successfully with same structure and data as old table "xyz". But for the column "salary" in old table "xyz" default value was set to "0". But in the newly created table "abc" the default value is not set. This is all in Oracle 11g. Please tell me the reason why the default value was not set and how we can set this using select statement.

[rski]

This is how CTAS works.
Maybe take a look at dbms_metadata.g et_ddl function. It will return the query used to create a table in this output query you need only to change table name and run.
Get a definition of TEST table

select dbms_metadata.get_ddl('TABLE','TEST') from dual
 

In the output you will se something like

 
  CREATE TABLE "RSKI"."TEST" 
   (    "I" NUMBER(*,0) DEFAULT 100
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" 
 

Just edit this query (rename table name) and run.

[debasisdas]

Yes you need to do as suggested by rski.

CREATE TABLE AS SELECT..... syntax

creates only the basic structure of the table without any contstraints.