Pages

Friday 22 July 2016

ORA-01792: maximum number of columns in a table or view is 1000

select statement throws ORA-01792 error in Oracle 12.1.0.2.0 version during application reload after migrating database from 11g to 12c.

Below is the error in the application logs.

2016-07-09 12:30:00,614 INFO  atomikos createCompositeTransaction ( 3600000 ): created new ROOT transaction with id 3.21.105.48.tm0009301224
2016-07-09 12:30:00,615 INFO  atomikos commit() done (by application) of transaction 3.21.105.48.tm0009301224
2016-07-09 12:30:01,564 WARN  com.svg.core.database.Connection ORA-01792: maximum number of columns in a table or view is 1000

2016-07-09 12:30:01,564 ERROR com.svg.core.database.Connection ORA-01792: maximum number of columns in a table or view is 1000 [Error=1792]
java.sql.SQLSyntaxErrorException: ORA-01792: maximum number of columns in a table or view is 1000

Reason:

The problem is due to Unpublished bugs:

Bug 19653859 - CI BACKPORT OF BUG 19509982 FOR INCLUSION IN DATABASE BP 12.1.0.2.2
Bug 19509982 - DISABLE FIX FOR RAISING ORA-1792 BY DEFAULT


Solution:

The workaround is to set "_fix_control"='17376322:OFF'

SQL> alter session set "_fix_control"='17376322:OFF' sid='*';

or at system level :

SQL> alter system set "_fix_control"='17376322:OFF' sid='*';

or
apply the Patch 19509982 if available for your platform and db version.


No comments:

Post a Comment