Locked Oracle Stats

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 🙂

Advertisements

About dnadba

I work with databases and stuff
This entry was posted in Oracle. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s