unlock and gather index stats

in 10.2, Oracle Database now automatically collects statistics during index creation and rebuild

COMPUTE STATISTICS In earlier releases, you could use this clause to start or stop the collection of statistics on an index. This clause has been deprecated. Oracle Database now automatically collects statistics during index creation and rebuild. This clause is supported for backward compatibility and will not cause errors.

but this is not true when the table statistics is lock, the index statistics can’t be gather until the table staticstics is unlocked

sys@dbinst1>  select index_name, index_type, last_analyzed from user_indexes where index_name like 'CR_SHMTRESULT_IDX04';

INDEX_NAME                     INDEX_TYPE                  LAST_ANALYZED
------------------------------ --------------------------- -------------------
CR_SHMTRESULT_IDX04            NORMAL                      2009-05-17 12:34:53

app_user1@dbinst1> exec dbms_stats.gather_index_stats(user,'CR_SHMTRESULT_IDX04');
BEGIN dbms_stats.gather_index_stats(user,'CR_SHMTRESULT_IDX04'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 10640
ORA-06512: at "SYS.DBMS_STATS", line 10664
ORA-06512: at line 1

 1* select table_name, STATTYPE_LOCKED from all_ind_statistics where index_name like 'CR_SHMTRESULT_IDX04'
app_user1@dbinst1> /

TABLE_NAME                     STATT
------------------------------ -----
CR_INTERMEDIATE_SHMTRESULT     ALL

app_user1@dbinst1>  select table_name, STATTYPE_LOCKED from all_tab_statistics where table_name like 'CR_INTERMEDIATE_SHMTRESULT';

TABLE_NAME                     STATT
------------------------------ -----
CR_INTERMEDIATE_SHMTRESULT     ALL

after unlock the table’s statistics, the index stats can be regather

app_user1@dbinst1> exec dbms_stats.unlock_table_stats(user,'CR_INTERMEDIATE_SHMTRESULT');

PL/SQL procedure successfully completed.

app_user1@dbinst1>  select table_name, STATTYPE_LOCKED from all_ind_statistics where index_name like 'CR_SHMTRESULT_IDX04'
  2  /

TABLE_NAME                     STATT
------------------------------ -----
CR_INTERMEDIATE_SHMTRESULT

app_user1@dbinst1> select table_name, STATTYPE_LOCKED from all_tab_statistics where table_name like 'CR_INTERMEDIATE_SHMTRESULT';

TABLE_NAME                     STATT
------------------------------ -----
CR_INTERMEDIATE_SHMTRESULT

app_user1@dbinst1> exec dbms_stats.gather_index_stats(user,'CR_SHMTRESULT_IDX04');

PL/SQL procedure successfully completed.

app_user1@dbinst1> select index_name, index_type, last_analyzed from user_indexes where index_name like 'CR_SHMTRESULT_IDX04';

INDEX_NAME                     INDEX_TYPE                  LAST_ANALYZED
------------------------------ --------------------------- -------------------
CR_SHMTRESULT_IDX04            NORMAL                      2010-03-24 16:17:33

finally, don’t forget to lock the table stats again

app_user1@dbinst1> exec dbms_stats.lock_table_stats(user,'CR_INTERMEDIATE_SHMTRESULT');

PL/SQL procedure successfully completed.

app_user1@dbinst1> select table_name, STATTYPE_LOCKED from all_ind_statistics where index_name like 'CR_SHMTRESULT_IDX04'
  2  /

TABLE_NAME                     STATT
------------------------------ -----
CR_INTERMEDIATE_SHMTRESULT     ALL

app_user1@dbinst1> select table_name, STATTYPE_LOCKED from all_tab_statistics where table_name like 'CR_INTERMEDIATE_SHMTRESULT';

TABLE_NAME                     STATT
------------------------------ -----
CR_INTERMEDIATE_SHMTRESULT     ALL