[20241012]ORA-01792 maximum number of columns in a table or view is 1000.txt

[20241012]ORA-01792 maximum number of columns in a table or view is 1000.txt --//12c数据库查询遇到的问题，几个大型视图，字段很多连接时出现问题。 ORA-01792: maximum number of columns in a table or view is 1000 --//上网查询以后发现设置alter session set "_fix_control"='17376322:OFF';可以解决。 SCOTT@book01p> column SESSION_ID format 9999999999 SCOTT@book01p> column event format 999999999 SCOTT@book01p> @ fix "1000 columns" SCOTT@book01p> @ pr ============================== SESSION_ID                    : 18 BUGNO                         : 17376322 VALUE                         : 0 SQL_FEATURE                   : QKSFM_CBO_17376322 DESCRIPTION                   : raise error if view(s) have more than 1000 columns OPTIMIZER_FEATURE_ENABLE      : EVENT                         : 0 IS_DEFAULT                    : 1 CON_ID                        : 3 PL/SQL procedure successfully completed. --//21c IS_DEFAULT=1 对应是off。 --//简单演示： 1.环境： SCOTT@book01p> @ver2 ============================== PORT_STRING                   : x86_64/Linux 2.4.xx VERSION                       : 21.0.0.0.0 BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production CON_ID                        : 0 PL/SQL procedure successfully completed. $ cat ct.txt set head off feedback off spool a.txt select 'create table t1 (' from dual union all select 'col'||lpad(rownum-1,3,'0')||' number(1),' from dual connect by level<=400 union all select 'constraint t1_pk primary key (col000));' from dual ; spool off set head on feedback 6 --//适当改名，建立3个表t1,t2,t3，每个表400字段，过程略，分析表略。 2.测试： SCOTT@book01p> alter session set "_fix_control"='17376322:ON'; Session altered. SCOTT@book01p> @ fix "1000 columns" SCOTT@book01p> @ pr ============================== SESSION_ID                    : 18 BUGNO                         : 17376322 VALUE                         : 0 SQL_FEATURE                   : QKSFM_CBO_17376322 DESCRIPTION                   : raise error if view(s) have more than 1000 columns OPTIMIZER_FEATURE_ENABLE      : EVENT                         : 0 IS_DEFAULT                    : 0 CON_ID                        : 3 PL/SQL procedure successfully completed. SCOTT@book01p> select t3.* from t1 join t2 on (t1.col000 = t2.col000) join t3 on (t2.col000 = t3.col000); select t3.* from t1 join t2 on (t1.col000 = t2.col000) join t3 on (t2.col000 = t3.col000)                                                             * ERROR at line 1: ORA-01792: maximum number of columns in a table or view is 1000 --//这样情况即使输出1个表t3也报错，如果使用非ansi连接写法不报错。 SCOTT@book01p> select t3.* from t1 ,t2,t3 where t1.col000 = t2.col000 and t2.col000 = t3.col000; no rows selected SCOTT@book01p> select * from t1 ,t2,t3 where t1.col000 = t2.col000 and t2.col000 = t3.col000; no rows selected SCOTT@book01p> alter session set "_fix_control"='17376322:OFF'; Session altered. SCOTT@book01p> @ fix "1000 columns" SCOTT@book01p> @ pr ============================== SESSION_ID                    : 18 BUGNO                         : 17376322 VALUE                         : 0 SQL_FEATURE                   : QKSFM_CBO_17376322 DESCRIPTION                   : raise error if view(s) have more than 1000 columns OPTIMIZER_FEATURE_ENABLE      : EVENT                         : 0 IS_DEFAULT                    : 1 CON_ID                        : 3 PL/SQL procedure successfully completed. --//IS_DEFAULT=1 SCOTT@book01p> select t3.* from t1 join t2 on (t1.col000 = t2.col000) join t3 on (t2.col000 = t3.col000); no rows selected SCOTT@book01p> select * from t1 join t2 on (t1.col000 = t2.col000) join t3 on (t2.col000 = t3.col000); no rows selected --//3个表一起输出也没有问题。 SCOTT@book01p> select * from t1 ,t2,t3 where t1.col000 = t2.col000 and t2.col000 = t3.col000; no rows selected