We're here to help!

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

Follow

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.

Have more questions? Submit a request

Comments