Skip to Main Content

Database Software


For appeals, questions and feedback, please email

ORA-01878: specified field not found in datetime or interval

RanjithKumarApr 3 2024

We do see below error message in the alertlog starting 5th March'2024 only between 7am CET to 8am CET not during other times.

we have MV refresh job runs every 30 mins.

MVRF: kkzlShrinkMVLog: Error -1878: ORA-01878: specified field not found in datetime or interval.

As per the trace, below statement is causing this error-

"insert into wri$_adv_objspace_trend_data select timepoint, space_usage, space_alloc, quality from table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, NULL, NULL, 'FALSE', :5, 'FALSE'))

I already disabled auto space advisor but still seeing this statement in trace.

SQL> select client_name,status from dba_autotask_client;

---------------------------------------------------------------- --------------------------------
auto optimizer stats collection ENABLED
sql tuning advisor ENABLED
auto space advisor DISABLED

Oracle doc says workaround is to set "_mv_refresh_shrink_log" = FALSE . I believe this will not shrink MV logs automatically and manual shrunk to be done.(i think not possible solution as it may cause storage fillup & i can't manually shrunk the logs frequently )

Does any one face the similar issue and what could be the solution?

Post Details
Added on Apr 3 2024