[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