Publish_pending_stats()/ export_pending_stats() not helping to remove duplicate savtime values as future dates in sys.wri$_optstat_ind_history
(Doc ID 2979789.1)
Last updated on JULY 20, 2024
Applies to:
Oracle Database - Enterprise Edition - Version 19.18.0.0.0 and laterGen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
On 19.18
------------
The issue is that export_pending_stats is not working due to the faulty SAVTIME. Not getting any error, but it creates savetime > current time in SYS.WRI$_OPTSTAT_IND_HISTORY.
DBMS_STATS.PURGE_ALL will preserve of the errant SAVTIME into a temporary table, then truncate the OPSTAT table, then load the saved-off pending stats back in. So we have no way to remove the errant SAVETIME.
Database with positive offset from gmt will not have correct stats exported.
There are entries in the pending stats with to_char(savtime,'mm/dd/yyyy')='12/01/3000' and savtime != FROM_TZ(TIMESTAMP '3000-12-01 01:00:00', '0:00');
Need how to remove the invalid data from the OPTSTAT tables. Queries are regressing and Several Database in 19c with the same incorrect pending stats data.
Changes
Upgraded from 11.2.0.4 to 19.18.0.0.0
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Changes |
| Cause |
| Solution |
| References |