Tuesday, September 16, 2008

ORA-38029: object statistics are locked

If this error occurs while trying to analyze a table then run following to unlock the statistics:

exec DBMS_STATS.UNLOCK_TABLE_STATS('{owner}','{table name}');

You can see list of all locked tables by running following query:

select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null and owner not in ('SYS','SYSTEM');

or

select 'exec DBMS_STATS.UNLOCK_TABLE_STATS('''||owner||''','''||table_name||''');' from dba_tab_statistics where stattype_locked is not null and owner not in ('SYS','SYSTEM')

For further detail, refer to metalink doc id 433240.1

2 comments:

Steve Harville said...

Good post! It saved me some time. Thanks

Рабочий said...

tnx: short and to the point