Friday, December 11, 2009

Gather Schema errors out with ORA-20005:

When running gather schema statistics, the following error occurs.
Error #1: ERROR: While GATHER_TABLE_STATS: object_name=APPLSYS.AQ$_WF_CONTROL_P***ORA-20005: object statistics are locked (stattype = ALL)***
Error #2: ERROR: While GATHER_TABLE_STATS: object_name=APPLSYS.FND_CP_GSM_IPC_AQTBL***ORA-20005: object statistics are locked (stattype = ALL)***
Error #3: ERROR: While GATHER_TABLE_STATS: object_name=APPLSYS.FND_CP_GSM_OPP_AQTBL***ORA-20005: object statistics are locked (stattype = ALL)***
Error #4: ERROR: While GATHER_TABLE_STATS: object_name=APPLSYS.FND_CP_TM_AQTBL***ORA-20005: object statistics are locked (stattype = ALL)***
Error #5: ERROR: While GATHER_TABLE_STATS: object_name=APPLSYS.FND_CP_TM_RET_AQTBL***ORA-20005: object statistics are locked (stattype = ALL)***
Error #6: ERROR: While GATHER_TABLE_STATS: object_name=APPLSYS.WF_SMTP_O_1_TABLE***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 is 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;

Unlock statistics gathering on those queues running the commands below.
- To unlock all the tables in a schema at once:

exec dbms_stats.unlock_schema_stats('schema_owner');
OR
To unlock individual tables (need to run for all tables individually):
exec dbms_stats.unlock_table_stats('table_owner','table_name');

Examples:
SQL> exec dbms_stats.unlock_schema_stats ('AR');

SQL> exec dbms_stats.unlock_table_stats('AR', 'AR_REV_REC_QT');

No comments: