在Oracle数据库运维过程中，有时候为了固定统计信息，运维人员会将相关表的统计信息锁定，这样，后期再添加索引时，往往会不起作用，容易对业务操作影响，具体测试如下： #### 环境搭建 ``` sql SQL> create table t1 as select *from all_objects; Table created. --锁定统计信息 SQL> exec dbms_stats.lock_table_stats(NULL,'T1'); PL/SQL procedure successfully completed. ``` #### 创建索引 ``` sql SQL> create index idx_t1_objectid on t1(object_id) online; Index created. ``` #### 检查索引，发现没有统计信息 ``` sql SQL> select owner,table_name,index_name,last_analyzed from dba_ind_statistics where table_name='T1'; OWNER TABLE_NAME INDEX_NAME LAST_ANAL --------------- --------------- --------------- --------- MYTEST T1 IDX_T1_OBJECTID ``` 以上，往往容易造成一个误解，索引已创建完成了，为什么无法使用呢 加上compute statistics 测试,显示统计信息已被锁定 ``` sql SQL> create index idx_t1_objectid on t1(object_id) online compute statistics; create index idx_t1_objectid on t1(object_id) online compute statistics * ERROR at line 1: ORA-38029: object statistics are locked ``` 去除锁定信息，再次创建索引 ``` sql SQL> exec dbms_stats.unlock_table_stats(null,'T1'); PL/SQL procedure successfully completed. SQL> drop index idx_t1_objectid; Index dropped. SQL> create index idx_t1_objectid on t1(object_id) online; Index created. SQL> select owner,table_name,index_name,last_analyzed from dba_ind_statistics where table_name='T1'; OWNER TABLE_NAME INDEX_NAME LAST_ANAL --------------- --------------- --------------- --------- MYTEST T1 IDX_T1_OBJECTID 19-NOV-21 ``` #### 其他 如果统计信息被锁定了，我们应该怎么收集统计信息呢，先解锁，再收集。或者使用以下命令参考： ``` sql exec dbms_stats.gather_table_stats(null,'T1',DEGREE=>4,no_invalidate=>FALSE,FORCE=TRUE); ```