Unlock locked statistics

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

ORA-06512: at “SYS.DBMS_STATS”, line 23154

ORA-06512: at “SYS.DBMS_STATS”, line 23205…

 
To unlock lock statistics for a table execute the following procedure:

exec DBMS_STATS.UNLOCK_TABLE_STATS('OWNER', 'TABLE_NAME');

Read my post about how to prevent statistics from becoming locked after expdp impdp

Preventing locked Statistics after expdp impdp

Cannot gather table statistics after data pump import impdp

exec dbms_stats.gather_table_stats(ownname=>'USER1',tabname=>'EMP');

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

ORA-06512: at “SYS.DBMS_STATS”, line 23154

ORA-06512: at “SYS.DBMS_STATS”, line 23205…

To unlock table Statistics see the following post:
Unlocking Table Statistics

I ran into this issue when I was exporting a schema from one database to import to another database.

I was not importing the rows only the objects and noticed the statistics came over. When trying to gather new statistic I received an error saying that the Statistics were locked. To prevent the statistic from being locked after importing I used the EXCLUDE STATISTICS parameter in the export.

This way the statistics are ignored and will not be locked on the new database.

In this example I want the entire schema with the exception of certain tables. I do not want Grants, rows, or statistics. Use the EXCLUDE STATISTICS parameter to prevent your statistics from being locked once you complete the import.

expdp_user1_tables.par

 

USERID=

DUMPFILE=user1_tables

LOGFILE=user1_tables

SCHEMAS=USER1

EXCLUDE=TABLE:"IN ('CONTRACT_TYPES','TIME_CODES','LAB_BOOK_ACCESS')",GRANT, TABLE_DATA

EXCLUDE=STATISTICS

Run the expdp to export the schema.

The following on one line:

dpdir> expdp parfile=expdp_user1_tables.par DIRECTORY=DATA_PUMP_DIR

ftp the dump file,  user1_tables.dmp, to the new database server.

impdp_user1_tables.par

USERID=

LOGFILE=imp_user1_tables

DUMPFILE=user1_tables

 dpdir> impdp parfile=imp_user1_tables.par DIRECTORY=DATA_PUMP_DIR

For more information on using Data Pump see the following post:

Using Data Pump 10g