SQL> alter system set fixed_date = '01-MAR-2011'
2 /
System altered.
SQL> select sysdate from dual
2 /
SYSDATE
---------
01-MAR-11
SQL>
Now create a table and analyze it:
SQL> create table andrews_table
2 as select * from dba_tables
3 /
Table created.
SQL> exec dbms_stats.gather_table_stats -
> (ownname => 'ANDREW', tabname => 'ANDREWS_TABLE');
PL/SQL procedure successfully completed.
SQL>
Check the date the table was last analyzed. This will match the sysdate set above:
SQL> select last_analyzed from user_tables
2 where table_name = 'ANDREWS_TABLE'
3 /
LAST_ANALYZED
-------------
01-MAR-11
SQL>
Before exporting the statistics, you need to create a table to hold them:
SQL> exec dbms_stats.create_stat_table -
> (ownname => 'ANDREW', stattab => 'ANDREWS_STATS');
PL/SQL procedure successfully completed.
SQL>
Now export the table's statistics. The stattab parameter tells Oracle where to store the statistics. The statid parameter gives this set of statistics a name within the table:
SQL> exec dbms_stats.export_table_stats -
> (ownname => 'ANDREW', tabname => 'ANDREWS_TABLE', -
> stattab => 'ANDREWS_STATS', statid => 'MAR2011');
PL/SQL procedure successfully completed.
SQL>
And you can see this identifier by running a select against the table storing the statistics:
SQL> select distinct statid from andrews_stats
2 /
STATID
------------------------------
MAR2011
SQL>
Now reinstate the sysdate:
SQL> alter system set fixed_date = 'NONE'
2 /
System altered.
SQL> select sysdate from dual
2 /
SYSDATE
---------
19-SEP-11
SQL>
And analyze the table again:
SQL> exec dbms_stats.gather_table_stats -
> (ownname => 'ANDREW', tabname => 'ANDREWS_TABLE');
PL/SQL procedure successfully completed.
SQL> /
SYSDATE
---------
19-SEP-11
SQL>
This sets the last_analyzed date to the new sysdate:
SQL> select last_analyzed from user_tables
2 where table_name = 'ANDREWS_TABLE'
3 /
LAST_ANALYZED
-------------
19-SEP-11
SQL>
dbms_stats.import_table_stats allows you to reimport the statistics which were exported earlier:
SQL> exec dbms_stats.import_table_stats -
> (ownname => 'ANDREW', tabname => 'ANDREWS_TABLE', -
> stattab => 'ANDREWS_STATS', statid => 'MAR2011');
PL/SQL procedure successfully completed.
SQL>
This has the effect of resetting the last_analyzed date to the earlier sysdate so that you can be sure that the correct statistics have been reimported:
SQL> select last_analyzed from user_tables
2 where table_name = 'ANDREWS_TABLE'
3 /
LAST_ANALYZED
-------------
01-MAR-11
SQL>
