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 […]
Read More ORA-38029 Object Statistics Are Locked – due to import with rows=nPurge and regather all optimizer stats with: exec dbms_stats.delete_database_stats exec dbms_stats.delete_system_stats exec dbms_scheduler.run_job(‘GATHER_STATS_JOB’) That works provided statistics level has not been changed to none show parameter statistics_level You can gather more detailed system stats with: exec dbms_stats.gather_system_stats(‘START’) exec dbms_stats.gather_system_stats(‘STOP’) You can see the system stats with: set pages 9999 lines 112 col pval2 form a20 […]
Read More Purge and regather all optimizer statsExamples of syntax to export/import dbms_stats: exec dbms_stats.create_stat_table ( ownname => user , stattab => ‘temp_stats’ ) ; exec dbms_stats.export_table_stats ( ownname => user , stattab => ‘temp_stats’ , tabname => ‘mytable’ , statid => ‘Taken_7Apr2010’ ) ; exec dbms_stats.import_table_stats ( ownname => user , stattab => ‘temp_stats’ , tabname => ‘mytable’ , statid => […]
Read More dbms_stats export import optimizer statisticscheck it is on with: select state, last_start_date from dba_scheduler_jobs where job_name = ‘GATHER_STATS_JOB’ ; Switch it on and off with: exec dbms_scheduler.disable(‘GATHER_STATS_JOB’) exec dbms_scheduler.enable(‘GATHER_STATS_JOB’)
Read More Automatic gather stats job