troubleshooting Question

MINUS query presents Oracle12c - ORA-01792: maximum number of columns in a table or view is 1000 error

Avatar of diteps06
diteps06Flag for United States of America asked on
Oracle DatabaseSQL
8 Comments1 Solution169 ViewsLast Modified:
We are experiencing the Oracle12c - ORA-01792: maximum number of columns in a table or view is 1000 after an upgrade from 11g to 12c which involves a MINUS query. The individual queries execute without the error.
In other words lets say the query is SELECT A, B FROM HN1 MINUS SELECT A, B FROM HN2
The statement SELECT A, B FROM HN1 executes without error. Likewise SELECT A, B FROM HN2.
However when they are combine the above error shows. There are about 18 columns in the query and when Oracle does internal query processing creating inline temporary tables it may strike the 1000 limit. At the moment I will not reduce the amount of columns in the query. Is there an Oracle hint I can use to make it function or any other strategy.

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros