통계정보 LOCK

http://m.blog.naver.com/shadouman/80122594664

STATTYPE_LOCKED 컬럼에 ALL 값이 존재하면 통계정보 생성을 하지 못하게 락이 걸려 있는것이며

값이 없다면 통계정보를 생성할 수 있는 것이다.

select OWNER,TABLE_NAME,STATTYPE_LOCKED from dba_tab_statistics
WHERE OWNER = 'SCOTT'
AND TABLE_NAME = 'EMP';

OWNER                          TABLE_NAME                     STATTYPE_LOCKED
------------------------------ ------------------------------ ---------------
SCOTT                           EMP                                                 ALL

OWNER                          TABLE_NAME                     STATTYPE_LOCKED
------------------------------ ------------------------------ ---------------
SCOTT                           EMP     

exec dbms_stats.lock_table_stats('SCOTT','EMP'); 

exec dbms_stats.unlock_table_stats('SCOTT','EMP');

ex) statistics lock 걸려 있을경우 아래와 같은 에러 발생됨.

SQL> exec dbms_stats.gather_table_stats('SCOTT','EMP');
BEGIN dbms_stats.gather_table_stats('SCOTT','EMP'); END;

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