Jaspersoft reports have been intermittently failing with 'ORA-08103: object no longer exists' errors. From the Oracle AWR report, it was found that the CMN_SAVEDROP_CREATE_INDEXES_SP stored procedure is running during the time.
What does this procedure do? And why would this cause the report to fail with this error?
The CMN_SAVEDROP_CREATE_INDEXES_SP procedure is executed when the Load Data Warehouses jobs run. The stored procedure drops the indexes before data is populated and then recreates them again.
The 'ORA-08103: object no longer exists' error may occur if the report is scheduled to run at the same time that the Load Data Warehouse Access Rights job runs. This is due to the job temporarily truncating and repopulating the DWH_INV_SECURITY table in the Data Warehouse at the same time that the report query is trying to access the data stored in this table (in order to confirm the user rights). It is recommended to either:
1. Run the Load Data Warehouse Access right job less often or at a different time.
2. Ask the users that run the reports that most frequently run into this error to schedule their reports to run at a different time. For example, if the Load DWH Access Rights job is set to start at 7:02AM, schedule the reports to run beforehand, at 6:45 or so, depending on how long the report takes to run.