DB
: Oracle 19.5
OS
: RHEL 7
When
try to run stats on table the following error appears
SQL>
execute dbms_stats.gather_table_stats(ownname => 'ADMIN', tabname
=>'CUST_TBL', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt
=> 'FOR ALL COLUMNS SIZEAUTO');
BEGIN
dbms_stats.gather_table_stats(ownname => 'ADMIN', tabname =>'CUST_TBL',
estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL
COLUMNS SIZEAUTO'); END;
*
ERROR
at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512:
at "SYS.DBMS_STATS", line 40751
ORA-06512:
at "SYS.DBMS_STATS", line 40035
ORA-06512:
at "SYS.DBMS_STATS", line 9393
ORA-06512:
at "SYS.DBMS_STATS", line 10317
ORA-06512:
at "SYS.DBMS_STATS", line 39324
ORA-06512:
at "SYS.DBMS_STATS", line 40183
ORA-06512:
at "SYS.DBMS_STATS", line 40732
ORA-06512:
at line 1
SQL>
select table_name, stattype_locked,last_analyzed from dba_tab_statistics where
owner='ADMIN' and table_name='CUST_TBL';
TABLE_NAME
STATT LAST_ANAL
----------------------
--------- ---------
CUST_TBL
ALL
06-APR-20
CUST_TBL
06-APR-20
Use below command to unlock the table
SQL>
exec dbms_stats.unlock_table_stats ('ADMIN','CUST_TBL');
PL/SQL
procedure successfully completed.
Elapsed:
00:00:01.31
SQL>
select table_name, stattype_locked from dba_tab_statistics where owner='ADMIN'
and table_name='CUST_TBL';
TABLE_NAME
STATT
----------------------
-----
CUST_TBL
CUST_TBL
SQL>
execute dbms_stats.gather_table_stats(ownname => 'ADMIN', tabname
=>'CUST_TBL', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt
=> 'FOR ALL COLUMNS SIZE AUTO');
PL/SQL
procedure successfully completed.
Elapsed:
00:00:05.00
To
find all table stats locked in a database:
SQL>
select owner,count(*) from dba_tab_statistics where stattype_locked is not null
group by owner;
OWNER
COUNT(*)
----------------------
----------
SYS
13
ADMIN
331
SCOTT
51
GSMADMIN_INTERNAL
1
GGOLD
2
5
rows selected.
You can generate
script to unlock all at once
select
'exec dbms_stats.unlock_table_stats (''’ ||a.owner || ‘'',''' ||
a.table_name || ''')' ||’;’ from dba_tab_statistics a
where
stattype_locked is not null and owner='<OWNER_NAME>';
0 comments:
Post a Comment