Before Oracle 23ai, the maximum allowed columns in a table is 1000. From Oracle 23ai you can increase this value by modifying a parameter MAX_COLUMNS up to 4096 if you have any use case. This initialization parameter can be set at system level only and in case of PDB you can limit to specific PDB.
To use this MAX_COLUMN parameter the compatibility should be set to 23.0.0.0 or higher. To increase max allowed colums you must set the MAX_COLUMNS value to “EXTENDED”. Note that you can change MAX_COLUMNS from STANDARD to EXTENDED any time but to change the value back to STANDARD only when any table or view in the database contains 1000 or fewer columns.
By default, the MAX_COLUMNS initialization parameter is set to STANDARD
SQL>show parameters max_columns
NAME TYPE VALUE
-------------------------------- ----------- -------------------------
max_columns string STANDARD
When user trying to add columns more than 1000 in a table will receive below error
ORA-01792: maximum number of columns in a table or view is 1000
You can change MAX_COLUMNS value using below command that will allow up to 4096 columnsSQL> shutdown immediate;
SQL> startup
If the database has tables with more than 1000 columns and trying to update the MAX_COLUMNS parameter value back to STANDARD, then user should receive below error
SQL> ALTER SYSTEM SET set MAX_COLUMNS =STANDARD scope=spfile;
ORA-32017: failure in updating SPFILE
ORA-60471: max_columns cannot be set to STANDARD as there are one or more objects with more than 1000 columns
The only way user can change this value by dropping the objects with more than 1000 columns.
Note that older Oracle client versions (before Oracle 23ai) do not support columns more than 1000 in a table and only Oracle 23ai clients support the 4096 column limit.
Thanks & Regards,
