统计信息的管理(三)锁定统计信息

以下四个过程为统计信息加锁和解锁

LOCK_SCHEMA_STATS

LOCK_TABLE_STATS

UNLOCK_SCHEMA_STATS

UNLOCK_TABLE_STATS

测试:

SQL> alter session set nls_date_format='yyyymmdd hh24:mi:ss';
 
Session altered.
 
SQL> select owner,table_name,last_analyzed from dba_tables                   
  2  where wner='SCOTT' and table_name='TEST';
 
OWNER                          TABLE_NAME                     LAST_ANALYZED
------------------------------ ------------------------------ -----------------
SCOTT                          TEST                           20090105 22:00:56

为统计信息加锁：
 
SQL>  exec dbms_stats.lock_table_stats('SCOTT','TEST');
 
PL/SQL procedure successfully completed.

锁定之后尝试对表进行统计信息收集，提示出错
 
SQL> exec dbms_stats.gather_table_stats('scott','test');
BEGIN dbms_stats.gather_table_stats('scott','test'); 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

为统计信息解锁：

SQL>  exec dbms_stats.unlock_table_stats('SCOTT','TEST');
 
PL/SQL procedure successfully completed.
 
SQL> exec dbms_stats.gather_table_stats('scott','test');
                                                      
PL/SQL procedure successfully completed.
 
SQL> select owner,table_name,last_analyzed from dba_tables
  2   where wner='SCOTT' and table_name='TEST';
 
OWNER                          TABLE_NAME                     LAST_ANALYZED
------------------------------ ------------------------------ -----------------
SCOTT                          TEST                           20090204 12:38:44

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10972173/viewspace-545226/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10972173/viewspace-545226/