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