I am using the CREATE TABLE AS SELECT statement in Oracle 11g to get data from SQL Server 2012 via a database link.

Oracle creates all these tables with non-nullable columns and that causes me problems later when I try to update them.

How can I prevent this behaviour in Oracle and make resulting columns nullable?


Either create the table manually beforehand, or specify the column names an NULLability in the CTAS statement:

create table blah2 
  ctascolumn1 not null,
  ctascolumn2 null
select col1, col2 from blah;
| improve this answer | |
  • Do they end up nullable because they come from a database link or is it always the case with CTAS regardless of the source? – Andriy M Dec 30 '15 at 13:31
  • Always the case, as far as I know – Philᵀᴹ Dec 30 '15 at 13:32
  • @AndriyM: I think Oracle decides this based on the values. If a column of the query only contains non null values, the table's column is defined with NOT NULL. If at least one value in the query result is null the column is defined as NULL – a_horse_with_no_name Dec 30 '15 at 13:48
  • @a_horse_with_no_name I doubt it. How would it know if there's a 4 Petabyte dataset? It won't scan the result set once to create, then again to insert – Philᵀᴹ Dec 30 '15 at 13:50
  • I'm pretty sure Oracle does scan the whole result because otherwise it won't be able to actually copy the data. But although I'm pretty sure I have seen this happening, I can't reproduce this on Oracle 12c – a_horse_with_no_name Dec 30 '15 at 13:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.