ORA-20000: ** FAILED TO GET COLUMN STATS FOR THE TABLE

SQL> BEGIN
STAT_MAINTENANCE.GATHER_SCHEMA_STATS_JOB('TESTUSER');
END;
/

BEGIN
*
ERROR at line 1:
ORA-20000: ** FAILED TO SET STAT_TIME FOR THE TABLE **: TESTUSER.TEST_TABLE
ERROR: ORA-20000: ** FAILED TO GET COLUMN STATS FOR THE TABLE **:
TESTUSER.TEST_TABLE Make sure Table has Standard Stats. ERROR: ORA-20000:
Unable to get values for column AVAILABLE_DATE
ORA-06512: at "SYS.DBMS_STATS", line 12211
ORA-06512: at "SYS.DBMS_STATS", line 12224
ORA-06512: at "STAT_MAINTENANCE", line 1072
ORA-06512: at "STAT_MAINTENANCE", line 1086
ORA-06512: at "STAT_MAINTENANCE", line 1171
ORA-06512: at "STAT_MAINTENANCE", line 1186
ORA-06512: at "STAT_MAINTENANCE", line 1645
ORA-06512: at "STAT_MAINTENANCE", line 1779
ORA-06512: at line 2



SQL> select * from user_tab_stat_prefs where table_name='TEST_TABLE';

TABLE_NAME   PREFERENCE_NAME      PREFERENCE_VALUE
------------ -------------------- ----------------------------
TEST_TABLE   ESTIMATE_PERCENT     DBMS_STATS.AUTO_SAMPLE_SIZE
TEST_TABLE   METHOD_OPT           FOR ALL COLUMNS SIZE AUTO,FOR COLUMNS "C1" SIZE 1,"C2" SIZE 1,"C3" SIZE 1,"C4" SIZE 1
  
SQL> select DBMS_STATS.GET_PREFS('METHOD_OPT','TESTUSER','TEST_TABLE') from dual;

DBMS_STATS.GET_PREFS('METHOD_OPT','TESTUSER','TEST_TABLE')
------------------------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO,FOR COLUMNS "C1" SIZE 1,"C2" SIZE 1,"C3" SIZE 1,"C4" SIZE 1


Describe the table and check if any of the columns were removed.

SQL> DESC TESTUSER.TEST_TABLE

 Name     Null?    Type
 -------- -------- ----------------------------
 P1       NOT NULL NUMBER(38)
 C1                NUMBER(38)
 T1                VARCHAR2(3 CHAR)
 T2                TIMESTAMP(6)
 C2                VARCHAR2(32 CHAR)
 C3                FLOAT(126)
 C5                VARCHAR2(255 CHAR)
 C7       NOT NULL NUMBER(38)

  --- from PREFERENCE_VALUE, remove column dropped and use it below
SQL> EXEC DBMS_STATS.DELETE_TABLE_PREFS ('TESTUSER', 'TEST_TABLE','METHOD_OPT');


SQL> BEGIN
dbms_stats.Set_table_prefs('TESTUSER', 'TEST_TABLE', 'METHOD_OPT' , 'FOR ALL COLUMNS SIZE AUTO,FOR COLUMNS C1 SIZE 1,C2 SIZE 1,C3 SIZE 1');  
END;
/


SQL> select * from user_tab_stat_prefs where table_name='TEST_TABLE';

TABLE_NAME   PREFERENCE_NAME      PREFERENCE_VALUE
------------ -------------------- --------------------------------------
TEST_TABLE   ESTIMATE_PERCENT     DBMS_STATS.AUTO_SAMPLE_SIZE
TEST_TABLE   METHOD_OPT           FOR ALL COLUMNS SIZE AUTO,FOR COLUMNS "C1" SIZE 1,"C2" SIZE 1,"C3" SIZE 1

SQL> select DBMS_STATS.GET_PREFS('METHOD_OPT','TESTUSER','TEST_TABLE') from dual;

DBMS_STATS.GET_PREFS('METHOD_OPT','TESTUSER','TEST_TABLE')
--------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO,FOR COLUMNS C1 SIZE 1,C2 SIZE 1,C3 SIZE 1



SQL> BEGIN
STAT_MAINTENANCE.GATHER_SCHEMA_STATS_JOB('TESTUSER');
END;

/