Andrew Fraser DBA

ORA-38029 Object Statistics Are Locked – due to import with rows=n

A strange one – but it is a documented feature in oracle.

If you import specifying option ‘rows=n’, then statistics for all imported tables will be locked after the import operation is finished.

That is only for import – an export with ‘rows=n’ is fine, doesn’t lock anything.

That’s probably not what you want to happen – it means that ‘dbms_stats gather’ operations and ‘analyze’ operations will not update optimizer statistics on those tables.

Avoid this by either:

• Specifying option ‘rows=n’ on the export only, not on the import (what I always do anyway); or
• Using DBMS_STATS.UNLOCK_[SCHEMA|TABLE]_STATS to correct after the import is finished.

This is true for all versions from 10gR2 onwards, and it is documented:

• 11gR2 Utilities Manual
• 11gR1 Utilities Manual
• 10gR2 Database Readme

It’s not just old style import – data pump import behaves similarly.

You can see what optimizer statistics are locked in your database with:

select owner, table_name, stattype_locked
from dba_tab_statistics
where stattype_locked is not NULL
order by 1,2 ;

Use DBMS_STATS.UNLOCK_[SCHEMA|TABLE]_STATS and DBMS_STATS.LOCK_[SCHEMA|TABLE]_STATS to switch on and off outside import.

Shell script to demonstrate this:

echo Create table
sqlplus -s "/ as sysdba" <<END_SQL
create table myschema.demo1 ( x number) ;
select stattype_locked from dba_tab_statistics where owner = 'MYSCHEMA' and table_name = 'DEMO1' ;
END_SQL

echo Export
exp \"/ as sysdba\" tables=myschema.demo1 rows=n

echo Drop table
sqlplus -s "/ as sysdba" <<END_SQL
drop table myschema.demo1 ;
END_SQL

echo Import - stats ok
imp \"/ as sysdba\" full=y rows=n
sqlplus -s "/ as sysdba" <<END_SQL
select stattype_locked from dba_tab_statistics where owner = 'MYSCHEMA' and table_name = 'DEMO1' ;
END_SQL

echo Drop table again
sqlplus -s "/ as sysdba" <<END_SQL
drop table myschema.demo1 ;
END_SQL

echo Import this time with rows=n - now stats are locked
imp \"/ as sysdba\" full=y
sqlplus -s "/ as sysdba" <<END_SQL
select stattype_locked from dba_tab_statistics where owner = 'MYSCHEMA' and table_name = 'DEMO1' ;
END_SQL

echo Tidy up
sqlplus -s "/ as sysdba" <<END_SQL
drop table myschema.demo1 ;
END_SQL