ORA-01792: maximum number of columns in a table or view is 1000

Owned by Clive Otten (Unlicensed)
Jan 21, 2020
1 min read

This error can be seen on Target Oracle 12 database, if the following almost not realistic things will happen together:

  • Customer has table ‘customer_table’ with M < 1000 columns.

  • Customer decided to add 1000-M columns to his ‘customer_table’ (so at the end he will have a table ‘customer_table’ with 1000 columns). 

  • Also customer decided that at least one column ‘new_column_name’ is not null and has the default value.

So customer did the following (M=2 for example): 
‘alter table customer_table add (new_column_name number(2) default 0 not null, new_column_name_2 number(2))’

The statement will be correctly processed on source database, but we will see the conflict, because 
On target database we would like to do 2 alter table statements instead of one.
The first one is ‘alter table customer_table add (new_column_name number(2) DEFAULT 0, new_column_name_2 number(2))
The second one is ‘alter table customer_table modify (new_column_name number(2) default 0 not null, new_column_name_2 number(2))
But the first statement will fail, because internally Oracle would create additional internal hidden column. And technically we are trying to insert 3 columns (2 customer columns and 1 internal oracle column) and we will hit the 1000 column number limitation.

Proposed solution: if default value is not sysdate you can manually execute customer statement.

If default value is sysdate for now we have no solution.