FIXED_DATE and LAST_ANALYZED

I tested this on Oracle 11.2.0.1. You need to be careful when looking at the LAST_ANALYZED column in USER_TABLES. I created a table and removed the FIXED_DATE parameter from the database:

 

SQL> create table t1 (c1 number)

  2  /

 

Table created.

 

SQL> alter system set fixed_date = none

  2  /

 

System altered.

 

SQL>

 

…then I used DBMS_STATS.GATHER_TABLE_STATS and the old ANALYZE command to create statistics for the table. Both of them set the LAST_ANALYZED column in USER_TABLES to the current date:

 

SQL> exec dbms_stats.gather_table_stats -

> (ownname=>'ORACLE',tabname=>'T1');

 

PL/SQL procedure successfully completed.

 

SQL> select last_analyzed

  2  from user_tables

  3  where table_name = 'T1'

  4  /

 

LAST_ANALYZED

-------------

10-SEP-14

 

SQL> analyze table t1 compute statistics

  2  /

 

Table analyzed.

 

SQL> select last_analyzed

  2  from user_tables

  3  where table_name = 'T1'

  4  /

 

LAST_ANALYZED

-------------

10-SEP-14

 

SQL>

 

I set FIXED_DATE to 25^th December 2014 and tried again:

 

SQL> alter system set fixed_date = '25-DEC-2014'

  2  /

 

System altered.

 

SQL>

 

DBMS_STATS.GATHER_TABLE_STATS set the LAST_ANALYZED column to the new FIXED_DATE value. I believe this is not what Oracle intended and may be a result of bug 8892343, which states:

 

The value of the LAST_ANALYZED column in USER_TABLES views is affected by the setting of the FIXED_DATE parameter when it should be independent of that value.

 

SQL> exec dbms_stats.gather_table_stats -

> (ownname=>'ORACLE',tabname=>'T1');

 

PL/SQL procedure successfully completed.

 

SQL> select last_analyzed

  2  from user_tables

  3  where table_name = 'T1'

  4  /

 

LAST_ANALYZED

-------------

25-DEC-14

 

SQL>

 

…whereas the old ANALYZE command set the LAST_ANALYZED column to the underlying system date:

 

SQL> analyze table t1 compute statistics

  2  /

 

Table analyzed.

 

SQL> select last_analyzed

  2  from user_tables

  3  where table_name = 'T1'

  4  /

 

LAST_ANALYZED

-------------

10-SEP-14

 

SQL>