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

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.

Source: oracle-base.com