发现用户的统计信息被锁定。

hr@(20_CBTDEPDB)> select owner,table_name,STATTYPE_LOCKED from dba_tab_statistics where owner='HR';
​
OWNER     TABLE_NAME  STATTYPE_LOCKED
-------------------- -------------------- --------------------
HR     REGIONS  ALL
HR     COUNTRIES  ALL
HR     LOCATIONS  ALL
HR     DEPARTMENTS  ALL
HR     JOBS  ALL
HR     EMPLOYEES  ALL
HR     JOB_HISTORY  ALL
HR     REGIONS_COL   ALL
HR     TBSPAR_ORDER  ALL
HR     REGIONS2  ALL
HR     REGIONS  ALL
HR     TAB1  ALL
HR     TDE1  ALL
HR     TBSPAR_ORDER  ALL
​
14 rows selected.
​
hr@(20_CBTDEPDB)>

解锁 schema下的所有统计信息

execute dbms_statS.unlock_schema_stats(ownname=>'HR');
select owner,table_name,STATTYPE_LOCKED from dba_tab_statistics where owner='HR';
hr@(20_CBTDEPDB)>  execute dbms_statS.unlock_schema_stats(ownname=>'HR');
 select owner,table_name,STATTYPE_LOCKED from dba_tab_statistics where owner='HR';
PL/SQL procedure successfully completed.
​
hr@(20_CBTDEPDB)>
​
OWNER     TABLE_NAME  STATTYPE_LOCKED
-------------------- -------------------- --------------------
HR     REGIONS
HR     COUNTRIES
HR     LOCATIONS
HR     DEPARTMENTS
HR     JOBS
HR     EMPLOYEES
HR     JOB_HISTORY
HR     REGIONS_COL
HR     TBSPAR_ORDER
HR     REGIONS2
HR     REGIONS
HR     TAB1
HR     TDE1
HR     TBSPAR_ORDER
​
14 rows selected.
​
hr@(20_CBTDEPDB)>

锁定schema统计

execute dbms_statS.lock_schema_stats(ownname=>'HR');
select owner,table_name,STATTYPE_LOCKED from dba_tab_statistics where owner='HR';

查看表锁定情况

select owner,table_name,STATTYPE_LOCKED from dba_tab_statistics where owner='HR';

锁定表统计

execute dbms_stats.lock_table_stats(ownname=>'HR',tabname=>'REGIONS');

收集统计信息将报错

hr@(20_CBTDEPDB)> execute dbms_stats.gather_table_stats(ownname=>'HR',tabname=>'REGIONS');
BEGIN dbms_stats.gather_table_stats(ownname=>'HR',tabname=>'REGIONS'); END;
​
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 34634
ORA-06512: at line 1
​
​
hr@(20_CBTDEPDB)>