星期五, 5月 24, 2013

[Ref] How to lock/unlock statistics on a table?

Ref:

– create table
SQL> create table test ( x number );
Table created.
– create index
SQL> create index test_idx on test(x);
Index created.
– shows when stats is not locked the value of stattype_locked is NULL
SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEST' and owner = 'SCOTT';
STATT
—–
– lock statistics
SQL> exec dbms_stats.lock_table_stats('scott', 'test');
PL/SQL procedure successfully completed.
– shows when stats is locked the value of stattype_locked is ALL
SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEST' and owner = 'SCOTT';
STATT
—–
ALL
— try to gather statistics on locked table
SQL> exec dbms_stats.gather_index_stats('scott', 'test_idx');
BEGIN dbms_stats.gather_index_stats('scott', 'test_idx'); END;
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 10640
ORA-06512: at "SYS.DBMS_STATS", line 10664
ORA-06512: at line 1
– try to gather statistics on the index using analyze
SQL> analyze index ajaffer.test_idx compute statistics;
analyze index ajaffer.test_idx compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked
– unlock statistics
SQL> exec dbms_stats.unlock_table_stats('scott', 'test');
PL/SQL procedure successfully completed.


沒有留言:

LinkWithin-相關文件

Related Posts Plugin for WordPress, Blogger...