STATS of table is STALE after gathering stats in Oracle

 STATS of table is STALE after gathering stats in Oracle

I tried to gather the stats of the table but after that gather still its in STALE state.

Solution

To Overcome the STALE state, we need to change the option of TABLE from gather Auto to gather stats

  1. Check the option of the table from gather stats package.
select dbms_stats.get_prefs('OPTIONS', 'HR', 'EMPLOYEES') OPTIONS from dual;
OPTIONS
-----------
GATHER AUTO
  1. Change the option from GATHER AUTO to GATHER
exec dbms_stats.set_table_prefs('HR', 'EMPLOYEES', 'OPTIONS', 'GATHER');

3. Check the option of the table from gather stats package.

select dbms_stats.get_prefs('OPTIONS', 'HR', 'EMPLOYEES') OPTIONS from dual;
OPTIONS
-----------
GATHER

Related posts

How to deal with ORA-00020: maximum number of processes (%s) exceeded

How to resolve ORA-01111 ORA-01110 ORA-01157 in a physical standby database 

Công cụ tự động khai báo datafile – Oracle