Why is Oracle is locking the statistics of my schema after import?

Question

My problem is that I have a schema where the statistics of all tables are locked.

I found on the Internet that I can unlock using the DBMS_STATS.UNLOCK_TABLE_STATS (SCHEMA_NAME) procedure.

What I need to know is the how Oracle determines when the statistics are going to be locked and when not, to avoiding these kind of situations.

Answer 1

From the documentation for the original import command:

If ROWS=n, then statistics for all imported tables will be locked after the import operation is finished.

And for data pump import:

Be aware that if you specify CONTENT=METADATA_ONLY, then any index or table statistics imported from the dump file are locked after the import operation is complete.

If you don't want the data then as an alternative to unlocking the statistics on all the imported objects you could leave CONTENT as ALL, and apply a query filter to the export instead to exclude all rows, e.g. QUERY=("WHERE 0=1").