6

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.

Mark Stewart
  • 1,962
  • 4
  • 20
  • 24
Aramillo
  • 3,116
  • 3
  • 20
  • 48
  • Are you sure someone didn't manually lock the statistics, on purpose? – Alex Poole May 06 '15 at 16:50
  • @AlexPoole When i imported the schema in my staging area all table statistics were locked, however in the original schema they are not. – Aramillo May 06 '15 at 17:34
  • 1
    Did you import the schema [with no data](http://andrewfraserdba.com/2011/03/08/ora-38029-object-statistics-are-locked-due-to-import-with-rowsn/)? Seems to affect both imp and imdp, but might be useful to know which you used, and what parameters too. – Alex Poole May 06 '15 at 17:41
  • Notice too that index statistics will be locked too if the table statistics are locked. – Mark Stewart Jun 07 '16 at 19:13

1 Answers1

4

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

Alex Poole
  • 161,851
  • 8
  • 150
  • 257
  • Yes @Alex, looks that you solved my problem. I used `CONTENT=METADATA_ONLY` with `impdp` because i didn't need data, i will try next time using `QUERY` parameter. Thanks :) – Aramillo May 06 '15 at 18:22