Monday, January 11, 2016

How to check if a table statistics is locked

LIQY@ORCL1> select table_name from user_tables;

TABLE_NAME
------------------------------
T1

LIQY@ORCL1> select STATTYPE_LOCKED from user_tab_statistics;

STATT
-----


LIQY@ORCL1> exec dbms_stats.lock_table_stats('LIQY','T1');

PL/SQL procedure successfully completed.

LIQY@ORCL1> select STATTYPE_LOCKED from user_tab_statistics;

STATT
-----
ALL

LIQY@ORCL1> exec dbms_stats.UNlock_table_stats('LIQY','T1');

PL/SQL procedure successfully completed.

LIQY@ORCL1> select STATTYPE_LOCKED from user_tab_statistics;

STATT
-----