MAX_COLUMNS parameter in Oracle Database 23ai

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 columns

SQL> ALTER SYSTEM set MAX_COLUMNS=EXTENDED scope=spfile;
SQL> 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,

https://oracleracexpert.com