Howdy,

Trying to figure out why my auto stats jobs are failing perpetually.

select job_info
from dba_autotask_job_history
where client_name  = 'auto optimizer stats collection'
order by window_start_time desc;

"ORA-01841: (full) year must be between -4713 and +9999, and not be 0
ORA-06512: at "SYS.DBMS_STATS", line 51871
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 24430
ORA-06512: at "SYS.DBMS_STATS", line 51859
"

Best I can tell this is happening on function based indexes applying trunc(<column_name>). I'm guessing this because if I run stats manually and pull up the real time monitor I can see big red X's for some indexes that are function based utilizing TRUNC to a date column.

Checked oracle support and found Auto Statistics Advisor fails with ORA-01841: (full) Year Must Be Between -4713 And +9999 (Doc ID 2771841.1) but neither of the problem definitions seem to be my problem.

SELECT name, ctime, how_created
         FROM sys.wri$_adv_tasks
         WHERE owner_name = 'SYS' and name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');

AUTO_STATS_ADVISOR_TASK         17-apr-2019 01:32:53 CMD
INDIVIDUAL_STATS_ADVISOR_TASK 17-apr-2019 01:32:53 CMD

Any guidance would be greatly appreciated, thanks!

create table t (
  c1 varchar2(20), c2 date as ( to_date ( c1, 'dd-mon-yyyy' ) )
);

insert into t ( c1 ) values ( '01-JAN-0000' );
commit;

select * from t;

exec dbms_stats.gather_table_stats ( user, 't' ) ;

ORA-20011: Approximate NDV failed: 
ORA-01841: (full) year must be between -4713 and +9999, and not be 0