ORACLE 解锁统计信息

发表于 分类于 ，

[toc]

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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下的所有统计信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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统计

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

查看表锁定情况

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

锁定表统计

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

收集统计信息将报错

1
2
3
4
5
6
7
8
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)>