ORACLE 11g新特性-統計值掛起

查詢資料庫級的統計值的公開屬性（PUBLISH） :true(為公開)，false(為私有)

SQL> SELECT DBMS_STATS.get_prefs('PUBLISH') FROM DUAL;
 
DBMS_STATS.GET_PREFS('PUBLISH'
--------------------------------------------------------------------------------
TRUE

 

查詢表級的統計值的公開屬性

SQL> SELECT DBMS_STATS.get_prefs('PUBLISH','SCOTT','EMP') FROM DUAL;
 
DBMS_STATS.GET_PREFS('PUBLISH'
--------------------------------------------------------------------------------
TRUE

 

設定表級的統計值PUBLISH屬性為私有:

SQL> EXEC DBMS_STATS.set_table_prefs('SCOTT',tabname => 'EMP',pname => 'PUBLISH',pvalue => 'FALSE');
 
PL/SQL procedure successfully completed

 

SQL> exec dbms_stats.delete_table_stats(ownname => 'SCOTT',tabname => 'EMP');
 
PL/SQL procedure successfully completed
 
SQL> exec dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname => 'EMP');
 
PL/SQL procedure successfully completed
 
SQL> SELECT TABLE_NAME,LAST_ANALYZED,NUM_ROWS FROM
  2  USER_TABLES
  3  WHERE TABLE_NAME='EMP';
 
TABLE_NAME                     LAST_ANALYZED   NUM_ROWS
------------------------------ ------------- ----------