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.

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:



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...