DBA Hub

📋Steps in this guide1/3

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

Oracle 23ai/26ai introduced the MAX_COLUMNS initialization parameter, which allows us to have up to 4096 columns in a table.

oracle 23configurationintermediate
by OracleDba
13 views
1

The Problem

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
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>

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>
2

The Solution : MAX_COLUMNS

The maximum number of columns is controlled by the initialization parameter, which has a default value of "STANDARD". We set the value to "EXTENDED" in the PDB and restart the PDB. We can now create a table with up to 4096 columns. We can't revert the setting of while we have one or more tables with more than 1000 columns. If we drop the table, we can reset the value.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

show parameters max_columns

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

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

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

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>

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>

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;
3

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 parameter can't be set at session level, only system level, but it can be limited to a specific PDB. - The 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 (23ai 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...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!