IT博客汇 | Oracle 23ai 表和视图的列最多支持到4096个

Oracle 23ai 表和视图的列最多支持到4096个

惜分飞发表于 2024-08-05 14:17:13

根据经验,oracle在以前常用版本中(包含oracle 19c),表和视图支持最大的列数量为1000,在oracle 23ai中允许支持最大列数量为4096,具体参见:23ai New Feature – Increased RDBMS Table/View Column Limit to 4096 (Doc ID 2947033.1),这里做了简单的试验,确认如果要支持4096列,需要设置max_columns=’EXTENDED’
准备测试表1000列、4096列和4097列

create table t_xff_col_1000(
col1 number,
col2 number,
col3 number,
col4 number,
……
col1000 number
);


create table t_xff_col_4096(
col1 number,
col2 number,
col3 number,
col4 number,
……
col4096 number
);

create table t_xff_col_4097(
col1 number,
col2 number,
col3 number,
col4 number,
……
col4097 number
);

[oracle@xifenfei ~]$ cat tab_col_4096.sql |grep col|grep -v xff|wc -l
4096
[oracle@xifenfei ~]$ cat tab_col_1000.sql |grep col|grep -v xff|wc -l
1000
[oracle@xifenfei ~]$ cat tab_col_4097.sql |grep col|grep -v xff|wc -l
4097

在max_columns为默认值的情况下(STANDARD)23ai版本中最多也只能支持1000列

[oracle@xifenfei ~]$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Mon Aug 5 22:01:57 2024
Version 23.5.0.24.07

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Version 23.5.0.24.07

SQL> show parameter max_co;

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
max_columns                          string
STANDARD

SQL> @tab_col_1000.sql

Table created.

SQL> @tab_col_4096.sql
        col1001 number,
        *
ERROR at line 1002:
ORA-01792: maximum number of columns in a table or view is 1000

在max_columns为EXTENDED的情况下能够支持列4096

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


SQL>  alter system set max_columns='EXTENDED' scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.

ORACLE instance shut down.
SQL> SQL> startup 
ORACLE instance started.

Total System Global Area 2413360688 bytes
Fixed Size                  5363248 bytes
Variable Size             570425344 bytes
Database Buffers         1828716544 bytes
Redo Buffers                8855552 bytes
Database mounted.
Database opened.
SQL> show parameter max_co;

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
max_columns                          string
EXTENDED
SQL> @tab_col_4096.sql

Table created.

SQL> select table_name,count(1) from dba_tab_cols where table_name like 'T_XFF%' GROUP BY TABLE_NAME;

TABLE_NAME
--------------------------------------------------------------------------------
  COUNT(1)
----------
T_XFF_COL_4096
      4096

T_XFF_COL_1000
      1000

SQL> @tab_col_4097.sql
create table t_xff_col_4096(
*
ERROR at line 1:
ORA-01792: maximum number of columns in a table or view is 4096