8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

MAX_COLUMNS : Increase the Maximum Number of Columns for a Table (Wide Tables) in Oracle Database 23c

Oracle 23c introduced the MAX_COLUMNS initialization parameter, which allows us to have up to 4096 columns in a table. This is sometimes described as wide tables.

The Problem
The Solution : MAX_COLUMNS
Considerations

The Problem

By default the maximum number of columns allowed for a table is 1000. The following code creates a table called T1 with 1000 columns with the name "COLn", where "n" is a number from 1 - 1000.

conn testuser1/testuser1@//localhost:1521/freepdb1

declare
  l_col_count number := 1000;
  l_str       clob;
begin
  execute immediate 'drop table if exists t1 purge';
  
  l_str := 'create table t1 (';
  for i in 1 .. l_col_count loop
    l_str := l_str || 'col' || to_char(i) || ' number, ';
  end loop;
  l_str := substr(l_str, 1, length(l_str)-2);
  l_str := l_str || ')';
  
  execute immediate l_str;
end;
/

desc t1

SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER
 COL2                                               NUMBER
 COL3                                               NUMBER
... edited for brevity ...
 COL998                                             NUMBER
 COL999                                             NUMBER
 COL1000                                            NUMBER

SQL>

This time we will try 1001 columns.

declare
  l_col_count number := 1001;
  l_str       clob;
begin
  execute immediate 'drop table if exists t1 purge';
  
  l_str := 'create table t1 (';
  for i in 1 .. l_col_count loop
    l_str := l_str || 'col' || to_char(i) || ' number, ';
  end loop;
  l_str := substr(l_str, 1, length(l_str)-2);
  l_str := l_str || ')';
  
  execute immediate l_str;
end;
/

declare
*
ERROR at line 1:
ORA-01792: maximum number of columns in a table or view is 1000
ORA-06512: at line 14


SQL>

The Solution : MAX_COLUMNS

The maximum number of columns is controlled by the MAX_COLUMNS initialization parameter, which has a default value of "STANDARD".

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

show parameters max_columns

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_columns                          string      STANDARD
SQL>

We set the MAX_COLUMNS value to "EXTENDED" in the PDB and restart the PDB.

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

alter system set max_columns=EXTENDED scope=spfile;
shutdown immediate;
startup;

We can now create a table with up to 4096 columns.

conn testuser1/testuser1@//localhost:1521/freepdb1

declare
  l_col_count number := 4096;
  l_str       clob;
begin
  execute immediate 'drop table if exists t1 purge';
  
  l_str := 'create table t1 (';
  for i in 1 .. l_col_count loop
    l_str := l_str || 'col' || to_char(i) || ' number, ';
  end loop;
  l_str := substr(l_str, 1, length(l_str)-2);
  l_str := l_str || ')';
  
  execute immediate l_str;
end;
/

SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER
 COL2                                               NUMBER
 COL3                                               NUMBER
... edited for brevity ...
 COL4094                                            NUMBER
 COL4095                                            NUMBER
 COL4096                                            NUMBER

SQL>

We can't revert the setting of MAX_COLUMNS while we have one or more tables with more than 1000 columns.

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

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


SQL>

If we drop the table, we can reset the value.

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

drop table if exists testuser1.t1 purge;

alter system set max_columns=STANDARD scope=spfile;
shutdown immediate;
startup;

Considerations

Some things to consider about this functionality.

Most of the time 1000 columns is more than enough, but occasionally people have use cases where more would be preferable.
The MAX_COLUMNS parameter can't be set at session level, only system level, but it can be limited to a specific PDB.
The MAX_COLUMNS parameter must be the same on all instances in a RAC cluster.
Having a large number of columns is likely to result in row chaining, even on a clean insert, and increased row migration, depending on the lifecycle of a row.
The "scope=memory" option has been disallowed to force a restart so every subsystem sees the new setting consistently. Thanks to Roger MacNicol for pointing this out.
We must use a compatible client (23c or above) to use this functionality.
The limit of 4096 columns includes virtual columns.
When using wide tables with HCC, you must use HCC Archive Low compression, rather than the default Query High.

For more information see:

MAX_COLUMNS

Hope this helps. Regards Tim...

Back to the Top.