CODEWITHSUNDEEP
oracle-databaseimportdatapumptable-statistics
Aramillo
Aramillo

Reputation: 3216

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

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.

Upvotes: 6

Views: 2987

Answers (1)

Alex Poole
Alex Poole

Reputation: 191295

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

Upvotes: 4

Related Questions