Oracle Wide Table – Oracle database 23c New Feature

MAX_COLUMNS

MAX_COLUMNS is new parameter available from 23c. Wide table is one of the new features of 23c, which allows a table to have 4096 number of columns which are limited to 1000 columns in previous versions. To enable wide tables, MAX_COLUMNS is a new parameter specifies the maximum number of columns allowed in database tables and views. COMPATIBLE initialization parameter must be 23.0.0.0 or higher to set MAX_COLUMNS = EXTENDED.

Parameter          - MAX_COLUMNS
Parameter type - String
Syntax - MAX_COLUMNS = { STANDARD | EXTENDED }
Default value - STANDARD
Modifiable - No
PDB Modifiable - Yes
Oracle RAC - All instances must use the same value.

MAX_COLUMNS = STANDARD will be default and limits to 1000 columns.
MAX_COLUMNS = EXTENDED allows to have 4096 columns.

How to enable database to allow 4096 columns?

SQL> alter session set container=FREEPDB1;
Session altered.
SQL> sho parameter MAX_COLUMNS;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_columns                          string      STANDARD
SQL>
SQL> alter system set MAX_COLUMNS='EXTENDED' sid='*' scope=spfile;
System altered.

SQL> alter pluggable database close immediate;
Pluggable database altered.

SQL> alter pluggable database FREEPDB1 open;
Pluggable database altered.

SQL> sho parameter MAX_COLUMNS;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_columns                          string      EXTENDED
SQL>

How to test after enabling MAX_COLUMNS to extended?

Run below anonymous block to generate a create table DDL statement with 4096 number of columns. Copy the table DDL and execute to create table with 4096 columns.

SQL> set serveroutput on;
begin 
dbms_output.put_line('create table TEST4096 (');
for i in 1..4096 loop
if i < 4096 then
dbms_output.put_line('A'||i||' '||' varchar2(10),');
else
dbms_output.put_line(replace(('A'||i||' '||' varchar2(10),'), ',', ');'));
end if;
end loop;
end;
/
SQL> create table TEST4096 (
A1  varchar2(10),
A2  varchar2(10),
.
.
A4095  varchar2(10),
A4096  varchar2(10));

Table TEST4096 created.

SQL> sho parameter MAX_COLUMNS;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_columns                          string      EXTENDED
SQL> select TABLE_NAME, count(1) TOTAL_COLUMNS from dba_tab_columns group by table_name having count(1) = 4096;
TABLE_NAME             TOTAL_COLUMNS
-------------------- ---------------
TEST4096                        4096
SQL>

How to Revert MAX_COLUMNS to STANDARD?

We can change MAX_COLUMNS from STANDARD to EXTENDED at any time, but to change from EXTENDED to STANDARD we need to make sure table or view should have less than 1000 columns.

SQL> select TABLE_NAME, count(1) from dba_tab_columns group by table_name having count(1) = 4096;
TABLE_NAME             COUNT(1)
-------------------- ----------
TEST4096                   4096

SQL> drop table SCOTT.TEST4096 purge;
Table dropped.

SQL> sho parameter MAX_COLUMNS;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_columns                          string      EXTENDED
SQL>
SQL> alter system set MAX_COLUMNS='STANDARD' sid='*' scope=spfile;
System altered.

SQL>  alter pluggable database close immediate;
Pluggable database altered.

SQL>  alter pluggable database FREEPDB1 open;
Pluggable database altered.

SQL> show parameter MAX_COLUMNS;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_columns                          string      STANDARD
SQL>SELECT NAME, VALUE from v$system_parameter WHERE con_id=3 AND isdefault='FALSE';

Leave a Comment