ORA-01792 when upgrading / restoring using Oracle database

Products

• Clarity PPM On Premise

Issue/Introduction

During an upgrade using Oracle database, receive an error when restoring a data backup.

We are seeing one view which is invalid and shows the exception, view has less than 20 columns.

Info: 1 view had errors on compile

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

For example, the creation of the database views ODF_PFM_INVESTMENT_CALCS_P_V and ODF_PFM_INVESTMENT_CALCS_P_V2 fails with this error message. Other database views may also fail to be restored. 

Environment

Release : All Support Releases

Component : CA PPM APPLICATION

Resolution

To resolve this issue, use the following workaround: 

Run the following command with system privileges in Oracle:

ALTER SYSTEM SET "_fix_control"='17376322:OFF' 

IMPORTANT: Engage your database team before making any decision. Make sure it is a safe environment e.g., DEV or TEST. Make sure you have a database backup before any action.

Notes:

1. The impact to not fixing the view means that the view won't work as expected. Example, if the error is related to: ODF_PFM_INVESTMENT_CALCS_P_V2, one of the portfolio views may not work as expected.
2. For more information about the fix_control parameter, see the following Oracle community discussion: _FIX_CONTROL Parameter
3. Apply the Oracle patch 19509982 to disable the error by default.

• • This patch is available from Oracle online support. An Oracle login is required. 

Additional Information

_FIX_CONTROL Parameter - Oracle Communities discussion on the fix_control parameter

Searching for known Clarity Issues using Self Service