Oracle datapump is pretty darn impressive, except in a few areas (e.g. having to use oracle diectories and having such a ridiculous name) but it did something a little unexpected to me today. I was creating a new database populated with 2 empty schemas from the production database (content=METADATA_ONLY). As the tables were empty I decided to gather fresh stats ..but it wouldn’t let me. The stats for every imported table were locked.
ORA-20005: object statistics are locked (stattype = ALL) ORA-06512: at "SYS.DBMS_STATS", line 23154 ORA-06512: at "SYS.DBMS_STATS", line 23205 ORA-06512: at line 2
I’ve no idea why impdp did this but it was easy to undo ofc:
SQL> exec dbms_stats.unlock_schema_stats('GSM_OWNER');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.unlock_schema_stats('GSM_USER');
PL/SQL procedure successfully completed.
A quick google afterwards showed that Oracle wasn’t just picking on me e.g. this guy also had this problem. Next time I do this the sensible thing to do would be to exclude stats from the export but I’ve seen databases running without stats and I break out in a cold sweat whenever I think about it 🙂
