Wednesday, December 7, 2011

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.


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.


 dpdir> impdp parfile=imp_user1_tables.par DIRECTORY=DATA_PUMP_DIR

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

No comments:

Post a Comment

Oracle runInstaller fails on Linux, Could not execute auto check for display colors using command /usr/bin/xdpyinfo. Check if the DISPLAY variable is set.

How to get Oracle's runInstaller to run on Linux or Unix environment using from Windows using Cygwin as an xterm emulator. Download an...