ORA-1792 in Oracle DB 12c

+ DB upgraded from Database Version 11.2 to 12.1
+ A query against a view returned the ORA-1792 error indicating that there are more than 1000 columns.
+

SQL> select t3.*
2 from t1
3 join t2 on (t1.id = t2.id)
4 join t3 on (t2.id = t3.id);
join t3 on (t2.id = t3.id)
*
ERROR at line 4:
ORA-01792: maximum number of columns in a table or view is 1000

+ The problem matches 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

REDISCOVERY INFORMATION:

See the error “ORA-01792: MAXIMUM NUMBER OF COLUMNS IN A TABLE OR VIEW IS 1000” when the original query does not select as many columns.

Solution
========

SQL> alter session set “_fix_control”=’17376322:OFF’;

or at system level :

SQL> alter system set “_fix_control”=’17376322:OFF’;

or

Apply Patch 19509982 if available for your DBVersion and Platform

Advertisements

Author: jee

Oracle Engineered Guy work for Oracle Corp. Techno-addict for Exadata, SuperCluster, ODA, RAC, ASM, HA.Fusion Music Enthusiast,Son,Husband,Father, Information-news- Freak, Optimist, Humanist. Interests : Technology, Innovation, Sharing interesting content. Views expressed on this account are my own and don't necessarily reflect the views of Oracle & its affiliates.