Load DWH error "ORA-01775: looping chain of synonyms"

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

When running Load Data Warehouse, an error is thrown in the bg-dwh.log:

An error occurred executing SQL:
SELECT CASE WHEN a.record_count > 0 THEN 1 ELSE 0 END record_exists
FROM (SELECT COUNT(1) record_count
FROM DWH_CUSTOM_OBJECT_NAME_V src
WHERE (CLARITY_UPDATED_DATE IS NULL OR CLARITY_UPDATED_DATE >= to_date('1910/01/01 00:00:00', 'yyyy/MM/dd HH24:mi:ss'))) a
[CA Clarity][Oracle JDBC Driver][Oracle]ORA-01775: looping chain of synonym

Environment

Release: Any Supported Release

Cause

Custom synonyms are created on the Clarity database on all objects. When synonyms exist, and you uncheck an object from Data Warehouse, when an object is unchecked, the view gets deleted in Clarity. If there is a synonym on it, that will cause a problem.
 

Resolution

Workaround

1. Go to Administration -> Objects
2. Enable the custom object as per the error message for Data Warehouse
   i.e. if the view name is DWH_TEST_V, the object name will be test
3. Check the object to be included in the Data Warehouse
4. Run a Full Load DWH job
5. Repeat the above for any additional custom objects

This workaround does not guarantee the problem will not reoccur in the future. If you would like to resolve the problem, please apply the solution below

Solution

Drop all custom synonyms from the Clarity database. Custom synonyms are not supported, and since you have errors, you must work with your DBA to drop them. This is the actual cause of the problem and might cause other issues with the application.
For more information check Custom synonyms in Clarity