[20240513]ORA-38029 object statistics are locked.txt

[20240513]ORA-38029 object statistics are locked.txt --//生产系统在建立索引时出现如上错误. ORA-38029 : object statistics are locked --//问题在于表统计上锁了,并且建立索引时加入compute statistics;,我个人建立索引喜欢在toad下生成建立脚本,在sqlplus下执行. --//如果建立时选上compute statistics并且表统计lock就会遇到这个问题.解决很简单取消建立的参数compute statistics. --//另外我还发现生产系统一些索引没有统计信息,估计一些表统计上锁的缘故. 1.环境: SCOTT@test01p> @ ver1 PORT_STRING          VERSION    BANNER                                                                       CON_ID -------------------- ---------- ---------------------------------------------------------------------------- ------ IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production      0 2.测试: SCOTT@test01p> create table empx as select * from emp; Table created. SCOTT@test01p> create unique index pk_empx on empx(empno) ; Index created. SCOTT@test01p>  @gts empx '' '' '' Gather Table Statistics for table empx... exec dbms_stats.gather_table_stats('SCOTT', 'EMPX', estimate_percent => NULL, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', cascade=>true, no_invalidate=>false) if lock table empx, add force=>true. press ctrl+c cancel, enter continue... PL/SQL procedure successfully completed. SCOTT@test01p> exec dbms_stats.lock_table_stats('SCOTT','EMPX'); PL/SQL procedure successfully completed. SCOTT@test01p> create index i_empx_ename on empx(ename) compute statistics; create index i_empx_ename on empx(ename) compute statistics                              * ERROR at line 1: ORA-38029: object statistics are locked SCOTT@test01p> create index i_empx_ename on empx(ename) ; Index created. SCOTT@test01p> @ ind2 i_empx_ename Display indexes where table or index name matches i_empx_ename... TABLE_OWNER TABLE_NAME INDEX_NAME   POS# COLUMN_NAME DSC ----------- ---------- ------------ ---- ----------- ---- SCOTT       EMPX       I_EMPX_ENAME    1 ENAME INDEX_OWNER TABLE_NAME INDEX_NAME   IDXTYPE UNIQ STATUS PART TEMP  H LFBLKS  NDK NUM_ROWS CLUF LAST_ANALYZED DEGREE VISIBILIT ----------- ---------- ------------ ------- ---- ------ ---- ---- -- ------ ---- -------- ---- ------------- ------ --------- SCOTT       EMPX       I_EMPX_ENAME NORMAL  NO   VALID  NO   N                                               1      VISIBLE --//没有统计信息!! --//单独分析索引,加入force=>true: SCOTT@test01p> exec dbms_stats.gather_index_stats(null, 'i_empx_ename',force=>true); PL/SQL procedure successfully completed. SCOTT@test01p> @ ind2 i_empx_ename Display indexes where table or index name matches i_empx_ename... TABLE_OWNER TABLE_NAME INDEX_NAME   POS# COLUMN_NAME DSC ----------- ---------- ------------ ---- ----------- ---- SCOTT       EMPX       I_EMPX_ENAME    1 ENAME INDEX_OWNER TABLE_NAME INDEX_NAME   IDXTYPE UNIQ STATUS PART TEMP  H LFBLKS NDK NUM_ROWS CLUF LAST_ANALYZED       DEGREE VISIBILIT ----------- ---------- ------------ ------- ---- ------ ---- ---- -- ------ --- -------- ---- ------------------- ------ --------- SCOTT       EMPX       I_EMPX_ENAME NORMAL  NO   VALID  NO   N     1      1  14       14    1 2024-05-15 21:44:48 1      VISIBLE --//有统计信息!! --//以后做运维注意这个问题. --//如果rebuild也类似: SCOTT@test01p> alter index i_empx_ename rebuild compute statistics online; alter index i_empx_ename rebuild compute statistics online * ERROR at line 1: ORA-38029: object statistics are locked SCOTT@test01p> alter index i_empx_ename rebuild  online; Index altered. SCOTT@test01p> @ ind2 i_empx_ename Display indexes where table or index name matches i_empx_ename... TABLE_OWNER TABLE_NAME INDEX_NAME   POS# COLUMN_NAME DSC ----------- ---------- ------------ ---- ----------- ---- SCOTT       EMPX       I_EMPX_ENAME    1 ENAME INDEX_OWNER TABLE_NAME INDEX_NAME   IDXTYPE UNIQ STATUS PART TEMP  H LFBLKS NDK NUM_ROWS CLUF LAST_ANALYZED       DEGREE VISIBILIT ----------- ---------- ------------ ------- ---- ------ ---- ---- -- ------ --- -------- ---- ------------------- ------ --------- SCOTT       EMPX       I_EMPX_ENAME NORMAL  NO   VALID  NO   N     1      1  14       14    1 2024-05-15 21:44:48 1      VISIBLE --//注意最后LAST_ANALYZED时间没有变化.