Pre-Oracle Database 23ai, there was a hard limit on the maximum number of columns in a table. The value was 1000.
In Oracle Database 23ai, there is a new feature called Wide Tables/Columns which removes the earlier 1000 column limit.
The new 23c limit is 4096 columns in a table.
A new parameter is available called MAX_COLUMNS with values STANDARD and EXTENDED.
Let us see an example of this new Oracle Database 23ai feature.
The widecols.sql CREATE TABLE script is creating a table with 1049 columns as shown.
CREATE TABLE HR.WIDECOLS
(
col1 CHAR ,
col2 CHAR ,
col3 CHAR ,
col4 CHAR ,
col5 CHAR ,
col6 CHAR ,
...
...
...
col1045 CHAR ,
col1046 CHAR ,
col1047 CHAR ,
col1048 CHAR ,
col1049 CHAR
);
The CREATE TABLE command fails as expected.
SQL> @widecols
col1001 CHAR ,
*
ERROR at line 1003:
ORA-01792: maximum number of columns in a table or view is 1000
Help: https://docs.oracle.com/error-help/db/ora-0179
The MAX_COLUMNS parameter can be set at the PDB level, but it is not a dynamic parameter.
SQL> alter system set max_columns=EXTENDED;
alter system set max_columns=EXTENDED
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this
option
Help: https://docs.oracle.com/error-help/db/ora-02096/
SQL> alter system set max_columns=EXTENDED scope=spfile;
System altered.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> alter pluggable database close immediate;
Pluggable database altered.
SQL> startup;
Pluggable Database opened.
After the PDB restart we can see the value for MAX_COLUMNS has now changed to EXTENDED.
We are now able to create the 1049 column value table!
SQL> show parameter max_columns
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_columns string EXTENDED
SQL> conn hr/oracle
Connected.
SQL> @widecols
Table created.
SQL> desc hr.widecols
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 CHAR(1)
COL2 CHAR(1)
COL3 CHAR(1)
COL4 CHAR(1)
COL5 CHAR(1)
COL6 CHAR(1)
...
...
...
COL1044 CHAR(1)
COL1045 CHAR(1)
COL1046 CHAR(1)
COL1047 CHAR(1)
COL1048 CHAR(1)
COL1049 CHAR(1)
If we try and revert the MAX_COLUMN parameter value to STANDARD from EXTENDED we receive an error as shown.
So if we even have a single table with more than 1000 columns in the database, we would not be able to change back to the default value of STANDARD – unless that table is dropped or columns in the table dropped to bring the value back to the allowed value of 1000.
SQL> alter system set max_columns=STANDARD scope=spfile;
alter system set max_columns=STANDARD scope=spfile
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-60471: max_columns can not be set to STANDARD as there are one or more
objects with more than 1000 columns
Help: https://docs.oracle.com/error-help/db/ora-32017/
