Gather stats fails after upgrade to Oracle 10G
Example:
ERROR: While GATHER_TABLE_STATS:
object_name=AR.AR_REV_REC_QT***ORA-20005: object statistics are locked (stattype = ALL)***
This can happen with Advance Queue tables.
In 10g, if a queue is created or recreated during the upgrade, automatic statistics gather gets locked (disabled) on these queue.
The following statement can be used to check the tables which have statistics locked:
select owner, table_name, stattype_locked
from dba_tab_statistics
where stattype_locked is not null;
SOLUTION:
Unlock gather stats running on those queues :
exec dbms_stats.unlock_schema_stats('schema_owner');
exec dbms_stats.unlock_table_stats('table_owner','table_name');
Example:
SQL> exec dbms_stats.unlock_schema_stats ('AR');
SQL> exec dbms_stats.unlock_table_stats('AR', 'AR_REV_REC_QT');
No comments:
Post a Comment