Load Data Warehouse failing with error ORA-01775: looping chain of synonyms

Document ID : KB000124384
Last Modified Date : 10/01/2019
Show Technical Document Details
Issue:
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 synonyms

 
Cause:
This is caused by:
The fact custom synonyms are created on the PPM database on all objects

While synonyms were there, a user unchecked an object from Data Warehouse
When an object is unchecked, the view gets deleted in PPM. If there is a synonym on it, that will cause a problem
 
Resolution:
Workaround
1. In Administration - Objects
2. Enable the custom object as per the error message for Data Warehouse
i.e. if view name is DWH_TEST_V, object name will be test
4. Check it to be included in Data Warehouse
5. Run a Full Load
6. Repeat for any additional custom objects
This workaround does not guarantee the problem will not reoccur in future. If you would like to resolve the problem, please apply the solution:

 
 
Solution
Drop all custom synonyms from the PPM 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.