[Oracle] "ORA-01792: maximum number of columns in a table or view is 1000" occurs - Known Error

[Oracle] "ORA-01792: maximum number of columns in a table or view is 1000" occurs Description In some cases, Oracle queries may result in the following error: [Oracle] "ORA-01792: maximum number of columns in a table or view is 1000"

This can happen on CMDB tables or other tables such as Task.

Steps to Reproduce

This error occurs on ALTER TABLE statements for large tables like CMDB, and sometimes on SELECT JOINs on Task (recursive from rm_story for example) even when the number of columns on the task did not exceed 1000 nor did the number of columns defined for output.

Workaround Choose from one of the following workarounds:

Try to specify a view with fewer columns. For more information, see the documentation topic Specify a field to return . Apply the fix supplied by Oracle. According to 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, you can disable the checking as follows: SQL> alter system set "_fix_control"='17376322:OFF'; Alternatively, you can apply an interim patch (patch 19509982) to disable the error by default. References: Bug 19509982 Disable raising of ORA-1792 by default ( https://support.oracle.com/epmos/faces/DocumentDisplay?id=19509982.8 ) Select Statement Throws ORA-01792 Error ( https://support.oracle.com/epmos/faces/DocumentDisplay?id=1951689.1 ) After this change restart the instance.

This fix has been available since about June 2017 and can be applied as a one-off patch.

October 2017 Patch Set Update and Bundle Patch Set updates that include this fix have been released. The Bundle Patch Set updates include optimizer fixes and are now Oracle's recommended patching method. However, they require uninstalling the Patch Set Update first before applying the Bundle Patch Set update. Related Problem: PRB732366