6

We've recently migrated a server from Oracle 11 to Oracle 12c and ran into a few annoying issues. One of them is that a few a reports give the following error:

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

OK, fine, I understand there have to be some limits. The problem is that the query worked before and that I see no reason for the DB to load all columns. None of tables in the query have more than 1000 columns. Combined they could have. So if I did a select * from everything; I understand that the output runs into limits because a result set is virtualy the same thing as a view/table (is it? I'm just guessing here?)

The "problem query" joins a bunch of big tables and a few smaller ones multiple times, so yes, the total column count could be more than 1000.

However, the query only selects 30 or so columns. Add the fields used for joining and maybe even some additional fields used by indices in the background it might add up to 50~70 columns that are needed to preform the query. All of that information is well defined within the query and table information. So why on earth would the DB even care about all the additional columns?

The solutions I've read so far are to change the query or to disable this warning/error. Both of which are non-solutions to me. The only reason I can think of for disabling the warning is that Oracle decided to inform users earlier and that this "problem" existed in the background in both versions.

Any ideas about this? Or my understanding of how 'big' queries are handled?

Improve this question
0

2 Answers 2

Reset to default
12

According to the My Oracle Support, ORA-01792 is caused due to an unpublished bug.

Oracle suggests to alter the query and/or view definitions to avoid the error. However in cases where the SQL cannot be adjusted then the checking can be disabled by: 

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

Alternatively, an interim patch(patch 19509982) can be applied to disable the error by default.

References:

  • Bug 19509982 Disable raising of ORA-1792 by default
  • Select Statement Throws ORA-01792 Error

    • Improve this answer
    1

    Changing to the "old" join syntax seems to help.

    From:

    select ...
from table1 t1
left join table2 on t1.id = t2.fid

    To:

    select ...
from table t1, table t2
where t1.id = t2.fid (+)
    Improve this answer
    1

    Not the answer you're looking for? Browse other questions tagged or ask your own question.