[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时间没有变化.