If you receive errors in the form of ORA-20005 and ORA-06512 during the collection of the table statistics, you must unlock the relevant table statistics.
When you run GATHER_TABLE_STATS, if it returns a result as follows you must unlock the statistics of the corresponding table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
BEGIN SYS.DBMS_STATS.GATHER_TABLE_STATS ( OwnName => 'OWNER' ,TabName => 'TABLE' ,Estimate_Percent => 100 ,Method_Opt => 'FOR ALL COLUMNS SIZE AUTO' ,Degree => 4 ,Cascade => TRUE ,No_Invalidate => FALSE); END; Error at line 1 ORA-20005: object statistics are locked (stattype = ALL) ORA-06512: location "SYS.DBMS_STATS", row 23829 ORA-06512: location "SYS.DBMS_STATS", row 23880 |
Unlocking table statistics:
You can use the following command for this operation.
1 |
SQL> exec dbms_stats.unlock_table_stats('OWNER', 'TABLE'); |