DBA Sensation

December 11, 2008

Using import (imp) or data pump import (impdp) to import a table without data results in the table’s statistics being locked in 10gR2

Filed under: [System Performance tuning] — zhefeng @ 9:03 am

when you import the table without data by using impdp, the statistics was locked after importing. Whatever you re-gather the statistics, the statistics won’t change and it won’t give your the locked error even.

The solution are varied from serveral way:
1. Gather the statistics with force=true
exec dbms_stats.unlock_schema_stats(ownname => 'WORLDDB9_REP', force => TRUE);

2. unlock the statistics using DBMS_STATS.UNLOCK_[SCHEMA|TABLE]_STATS, then gather statistics on the table using DBMS_STATS.GATHER_[SCHEMA|TABLE|INDEX]_STATS
EXEC DBMS_STATS.UNLOCK_TABLE_STATS(ownname => 'WORLDDB9_REP', tabname => 'TRIGGER_BASED');
exec dbms_stats.unlock_schema_stats(ownname => 'WORLDDB9_REP');

3. To prevent import (imp) from locking the table’s statistics when importing a table without the rows (rows=n), use statistics=none. To prevent data pump import (impdp) from locking the table’s statistics when importing a table without the rows (content=metadata_only), use exclude=(table_statistics,index_statistics).

Metalink: Note:433240.1

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: