使用dbms_stats列出沒有統計資訊的物件！

利用dbms_stats的procedure gather_schema_stats 和gather_database_stats透過使用引數options 和objlist可以列出那些沒有統計資訊或者統計資訊stale的物件。

[@more@]

其中options的值可以是：

Value	Meaning
GATHER STALE	Gathers statistics on tables with stale statistics.
GATHER	Gathers statistics on all tables. (default)
GATHER EMPTY	Gathers statistics only on tables without statistics.
LIST STALE	Creates a list of tables with stale statistics.
LIST EMPTY	Creates a list of tables that do not have statistics.
GATHER AUTO	Gathers all the statistics for the objects of a specific schema (or database with DBMS_STATS.GATHER_DATABASE_STATS()) that are not up-to-date.

The objlist parameter identifies an output parameter for the LIST STALE and LIST EMPTY options. The objlist parameter is of type DBMS_STATS.OBJECTTAB.

--===============================================

SQL> edit
已寫入 file afiedt.buf

1 declare
2 v_obj dbms_stats.objecttab;
3 begin
4 dbms_stats.gather_schema_stats
5 (OWNNAME=>'XYS',OPTIONS=>'LIST EMPTY',OBJLIST=>v_obj );
6 for i in 1 .. v_obj .count
7 loop
8 dbms_output.put_line( v_obj (i).ownname );
9 dbms_output.put_line( v_obj (i).objType );
10 dbms_output.put_line( v_obj (i).objName );
11 dbms_output.put_line( v_obj (i).PartName );
12 dbms_output.put_line( v_obj (i).subPartName );
13 dbms_output.put_line
14 ( '-------------------------' );
15 end loop;
16* end;
SQL> /
XYS
TABLE
MY_STAT_TAB
-------------------------
XYS
TABLE
T1
-------------------------
XYS
TABLE
TEST
-------------------------
XYS
TABLE
TEST1
-------------------------
XYS
TABLE
TT1
-------------------------
XYS
INDEX
IDX_T1
-------------------------
XYS
INDEX
MY_STAT_TAB
-------------------------

PL/SQL 過程已成功完成。

SQL> select table_name,num_rows,blocks,avg_row_len from user_tables;

TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
------------------------------ ---------- ---------- -----------
T1
TEST
TEST1
TT1
TT 5 5 8
MY_STAT_TAB

已選擇6行。

SQL> select index_name,blevel,leaf_blocks,clustering_factor from user_indexes;

INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ----------- -----------------
IDX_TT 0 1 1
SYS_IL0000016783C00026$$
MY_STAT_TAB
IDX_T1

SQL>