Limitations of Oracle 10g recovery statistics

There are also some limitations to the recovery statistics operation, such as the inability to restore user-defined statistics. In addition, because the statistics are the property of the table, if the table is deleted, the corresponding statistic backup is also cleared, and the truncate operation does not affect the statistics backup information:

Sql> CREATE TABLE T (ID number);

Table created.

Sql> INSERT into T SELECT rownum from TAB;

Rows created.

Sql> EXEC dbms_stats. Gather_table_stats (USER, ' T ')

Pl/sql procedure successfully completed.

Sql> SELECT table_name, num_rows, BLOCKS, last_analyzed

2 from User_tables

3 WHERE table_name = ' T ';

TABLE_NAME Num_rows BLOCKS last_analyzed

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

T 96 5 2 May-October-10

Sql> SELECT table_name, stats_update_time

2 from User_tab_stats_history

3 WHERE table_name = ' T ';

TABLE_NAME Stats_update_time

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

T 2 May-October -1010.55.10.109647 pm +08:00

sql> TRUNCATE TABLE T;

Table truncated.

Sql> INSERT into T-SELECT rownum from User_objects;

242 rows created.

Sql> COMMIT;

Commit complete.

Sql> EXEC dbms_stats. Gather_table_stats (USER, ' T ')

Pl/sql procedure successfully completed.

Sql> ALTER session SET Nls_date_format = ' yyyy-mm-dd HH24:MI:SS ';

Session altered.

Sql> SELECT table_name, num_rows, BLOCKS, last_analyzed

2 from User_tables

3 WHERE table_name = ' T ';

TABLE_NAME Num_rows BLOCKS last_analyzed

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

T 242 5 2010-10-25 22:56:39

Sql> SELECT table_name, stats_update_time

2 from User_tab_stats_history

3 WHERE table_name = ' T ';

TABLE_NAME Stats_update_time

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

T 2 May-October -1010.55.10.109647 pm +08:00

T 2 May-October-10 10.56.39.079646 pm +08:00

sql> DROP TABLE T PURGE;

Table dropped.

Sql> CREATE TABLE T (ID number);

Table created.

Sql> INSERT into T SELECT rownum from TAB;

Rows created.

Sql> COMMIT;

Commit complete.

Sql> EXEC dbms_stats. Gather_table_stats (USER, ' T ')

Pl/sql procedure successfully completed.

Sql> SELECT table_name, stats_update_time