The Load Data Warehouse (DWH) job is failing with an error referencing: ORA-00942: table or view does not exist, EXECUTE IMMEDIATE 'TRUNCATE TABLE DWH_CMN_ERROR_MESSAGE' in Clarity
This issue may be seen with the Load DWH job when it's run in Full or Incremental mode.
Example of error message below:
2018/08/24 11:13:53 - ClarityDB - isOracle? - Couldn't execute SQL: BEGIN
2018/08/24 11:13:53 - ClarityDB - isOracle? - EXECUTE IMMEDIATE 'TRUNCATE TABLE DWH_CMN_ERROR_MESSAGE';
2018/08/24 11:13:53 - ClarityDB - isOracle? - DWH_CFG_PRE_CONFIG_SP(
2018/08/24 11:13:53 - ClarityDB - isOracle? - P_DBLINK => 'ZZZZZZDWH_ZZZZZZ',
2018/08/24 11:13:53 - ClarityDB - isOracle? - P_FULL_RELOAD => 'Y');
2018/08/24 11:13:53 - ClarityDB - isOracle? - END;
2018/08/24 11:13:53 - ClarityDB - isOracle? -
2018/08/24 11:13:53 - ClarityDB - isOracle? - [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00942: table or view does not exist
2018/08/24 11:13:53 - ClarityDB - isOracle? - ORA-06512: at "ZZZZZZDDWH.DWH_CFG_PRE_CONFIG_SP", line 43
2018/08/24 11:13:53 - ClarityDB - isOracle? - ORA-06512: at line 3
Release: All supported Releases
Component: Clarity Data Warehouse (DWH)
You sometimes have a mismatch of tables during refresh if the database backup was taken when the Load Data Warehouse job was running on the source environment. We suggest you make sure you don't run the Load Data Warehouse at the times when the backups are taken for a refresh.
If the full load wasn't run first prior to running the Incremental job for any of the changes listed below, this can cause issues. Make sure to run the full load DWH job prior to incremental when any of the changes below occur (See more details at: Load DWH Full - When to run to prevent failures)
Important! This error is generic. To know the exact root cause and exact table that causes the issue, debugging with the database copy is required.
This can address this issue in a majority of instances. If this does not help proceed to the steps below.
If the above does not help: Re-build the DWH (reset the database to blank) using the steps found at: When to rebuild the Data Warehouse (DWH) database, then run the below-listed jobs.
Impact of rebuilding DWH: Make sure DWH Trend tables are backed up if you are using DWH trends as the trend data will be lost if the Data Warehouse schema is recreated. Once the DWH is rebuilt, replace
the backed up trend tabled in the newly imported schema
If there are entries they might be from a backup/restore operation done when the DWH job is running on the source environment.
If the above doesn't help, see: