PT - Lock | Unlock Optimizer Statistics Examples ORA-20005: object statistics are locked (stattype = ALL)

Scenario : 

 We Lock the Optimizer Statistics of tables  and then unlock the Optimizer Statistics of same table 

• Create table test2

SQL> CREATE TABLE TEST.TEST2 ( ORDER_ID  NUMBER(12), ORDER_DATE TIMESTAMP(6) WITH LOCAL TIME ZONE, ORDER_TOTAL NUMBER(8,2));

Table created.

• Insert values on test2 table

SQL> INSERT INTO TEST.TEST2 SELECT ORDER_ID, ORDER_DATE, ORDER_TOTAL FROM TEST.ORDERS WHERE ORDER_TOTAL >100;

1304324 rows created.

SQL> COMMIT;

Commit complete.

• Create index on table test2

SQL> CREATE INDEX TEST.TEST2_TOTAL_IX ON TEST.TEST2(ORDER_TOTAL);

Index created.

• Stats gathering of table test2

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TEST', TABNAME=>'TEST2', CASCADE=>TRUE);

PL/SQL procedure successfully completed.

• Check the locked staus of  table

SQL> SELECT STATTYPE_LOCKED, NUM_ROWS FROM USER_TAB_STATISTICS WHERE TABLE_NAME='TEST2';

STATT   NUM_ROWS

----- ----------

         1304324

• Lock table using   DBMS_STATS.LOCK_TABLE_STATS

SQL> exec DBMS_STATS.LOCK_TABLE_STATS('TEST','TEST2') ;

PL/SQL procedure successfully completed.

SQL>

• Check lock status using query

SQL> SELECT STATTYPE_LOCKED FROM USER_TAB_STATISTICS WHERE TABLE_NAME='TEST2';

STATT

-----

ALL

• Try to gather stats using DBMS_STATS.GATHER_TABLE_STATS it will through error

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TEST', TABNAME=>'TEST2', CASCADE=>TRUE);

BEGIN DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TEST', TABNAME=>'TEST2', CASCADE=>TRUE); END;

*

ERROR at line 1:

ORA-20005: object statistics are locked (stattype = ALL)

ORA-06512: at "SYS.DBMS_STATS", line 36873

ORA-06512: at "SYS.DBMS_STATS", line 36507

ORA-06512: at "SYS.DBMS_STATS", line 8582

ORA-06512: at "SYS.DBMS_STATS", line 9461

ORA-06512: at "SYS.DBMS_STATS", line 35836

ORA-06512: at "SYS.DBMS_STATS", line 36716

ORA-06512: at line 1

SQL>

• We can perform DML operations on table only affect on stats gathering

SQL> DELETE TEST.TEST2 WHERE ORDER_TOTAL>115;

1304048 rows deleted.

SQL> COMMIT;

Commit complete.

• Unlock the stats gathering

SQL> exec DBMS_STATS.UNLOCK_TABLE_STATS('TEST', 'TEST2');

PL/SQL procedure successfully completed.

• Execute stats gathering again to check status 

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TEST', TABNAME=>'TEST2', CASCADE=>TRUE);

PL/SQL procedure successfully completed.