CREATE TABLE AS SELECT creates non-nullable columns

Question

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?

Answer 1

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
) 
as 
select col1, col2 from blah;

• Andriy M: Do they end up nullable because they come from a database link or is it always the case with CTAS regardless of the source?
• Philᵀᴹ: Always the case, as far as I know
• user1822: @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
• Philᵀᴹ: @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
• user1822: 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