运行如下脚本,发现许多表不满足条件:Dba_Segments .blocks=Dba_Tables.Blocks+Dba_Tables.Empty_Blocks
SQL> SELECT ds.segment_type,ds.segment_name ,ds.blocks ds_block,dt.blocks dt_block,dt.empty_blocks dt_emp_block FROM Dba_Segments ds,dba_tables dt
2 WHERE ds.segment_name=dt.table_name
3 AND ds.blocks<>(dt.blocks+dt.empty_blocks)
4 AND ds.owner='TEST_USER'
5 AND dt.owner='TEST_USER'
6 AND ds.segment_name='ACCT';
SEGMENT_TYPE SEGMENT_NAME DS_BLOCK DT_BLOCK DT_EMP_BLOCK
------------------ --------------------------- ---------- ---------- ------------
TABLE ACCT 32 5 0
SQL>
这种现象,是由于统计信息不准确造成的,现在对表ACCT进行分析,计算统计信息:
SQL> analyze table acct compute statistics;
Table analyzed
SQL>
然后重新运行上面的查询:
SQL> SELECT ds.segment_type,ds.segment_name ,ds.blocks ds_block,dt.blocks dt_block,dt.empty_blocks dt_emp_block FROM Dba_Segments ds,dba_tables dt
2 WHERE ds.segment_name=dt.table_name
3 AND ds.blocks<>(dt.blocks+dt.empty_blocks)
4 AND ds.owner='TEST_USER'
5 AND dt.owner='TEST_USER'
6 AND ds.segment_name='ACCT';
SEGMENT_TYPE SEGMENT_NAME DS_BLOCK DT_BLOCK DT_EMP_BLOCK
------------------ --------------------------------- ---------- ---------- ------------
SQL>
上面的查询,没有返回结果,说明ACCT表满足如下条件:
Dba_Segments .blocks=Dba_Tables.Blocks+Dba_Tables.Empty_Blocks
可以使用如下脚本进行验证:
SQL> SELECT ds.segment_type,ds.segment_name ,ds.blocks ds_block,dt.blocks dt_block,dt.empty_blocks dt_emp_block FROM Dba_Segments ds,dba_tables dt
2 WHERE ds.segment_name=dt.table_name
3 AND ds.blocks=(dt.blocks+dt.empty_blocks)
4 AND ds.owner='TEST_USER'
5 AND dt.owner='TEST_USER'
6 AND ds.segment_name='ACCT';
SEGMENT_TYPE SEGMENT_NAME DS_BLOCK DT_BLOCK DT_EMP_BLOCK
------------------ --------------------------------- ---------- ---------- ------------
TABLE ACCT 32 5 27
SQL>