Monday, October 26, 2015

ORA-20005: object statistics are locked

Error “ORA-20005: object statistics are locked” means that table statistics are locked and hence stats can’t be gathered on this table. Stats are locked if we don’t want stats to be gathered on some or all of our tables.

Following example explains stats locking and unlocking process.

SQL> create table test_table (id number);

Table created.

SQL> insert into test_table values(1);

1 row created.

SQL> insert into test_table values(1);

1 row created.

SQL> commit;

Commit complete.

-- Check if stats are current locked or unlocked. NULL returned in column STATTYPE_LOCKED column means stats are not locked

SQL>  select STATTYPE_LOCKED from user_tab_statistics;

STATT
-----

-- Now we gather fresh stats on the table. We will be able to gather stats since stats are not locked
SQL> exec dbms_stats.gather_table_stats('test','test_table');

PL/SQL procedure successfully completed.

-- Now we lock the stats and try gathering stats

SQL> exec dbms_stats.lock_table_stats('test','test_table');

PL/SQL procedure successfully completed.

SQL>  select STATTYPE_LOCKED from user_tab_statistics;

STATT
-----
ALL

SQL> exec dbms_stats.gather_table_stats('test','test_table');
BEGIN dbms_stats.gather_table_stats('test','test_table'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 23829
ORA-06512: at "SYS.DBMS_STATS", line 23880
ORA-06512: at line 1

-- Now we unlock the stats again
SQL> exec dbms_stats.unlock_table_stats('test','test_table');

PL/SQL procedure successfully completed.

SQL>  select STATTYPE_LOCKED from user_tab_statistics;

STATT
-----


SQL> exec dbms_stats.gather_table_stats('test','test_table');

PL/SQL procedure successfully completed.

SQL>

No comments:

Popular Posts - All Times