:

ORA-20005: object statistics are locked

When using data pump to import a set of tables and data into an Oracle 10.2.0.4 database I received the following error:

ORA-20005: object statistics are locked (stattype = ALL)

In order to fix the issue, I used DBMS_STATS.UNLOCK_TABLE_STATS on each table of interest before running the import command. The statement takes the arguments of the owner and the table name. Here is an example:

EXEC DBMS_STATS.UNLOCK_TABLE_STATS(ownname=>'USER1', tabname=>'TABLE1')

You should note that all indexes stats on a table which has locked statistics will also be locked, and when gathering statistics the job will skip any segments with locked statistics. There is usually a switch in most commands to force gathering, deleting, etc on objects where the stats are locked so that is another way for you to get around it.

If you want to ensure that all of the tables and indexes in one schema are unlocked and can be altered, you should use the DBMS_STATS.UNLOCK_SCHEMA_STATS procedure instead. As the name implies, this will accutane side effects unlock and entire schema’s stats. Here is how you might use it:

EXEC DBMS_STATS.UNLOCK_SCHEMA_STATS('USER1')

You should be familiar with statistics in Oracle already, as well as gathering system statistics and gathering Oracle statistics in general.

Like it, share it...

Category: Data Pump


Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *