Tip#25 Fix for ORA-38029 : object statistics are locked

Recently while analyzing tables in one of our test database, we got ORA-38029: object statistics are locked. It turns out that in 10gR2, when you import (imp or impdp) table without data i.e. structure only, oracle will lock the table statistics. (ref : metalink doc id 433240.1)

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

select table_name, stattype_locked from dba_tab_statistics where owner = 'MBS' and stattype_locked is not null;

To generate unlock statement for all tables in the schema you can use following,

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

OR for each table individually the following,

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