今天朋友问我
dba_tables.blocks、dba_tables.empty_blocks和dba_segments.blocks之间是什么关系
我的感觉:
dba_segments.blocks 我常常用来查询高水位大小 (表真正使用的大小+碎片大小'使用后释放大小')
具体的dba_tables不是常用,因为他还要手动的去分析下, (直接用分析表通过查询就好)
http://www.itpub.net/thread-604366-1-1.html
2楼人讲的我觉得挺对的:
dba_tables.BLOCKS Number of used data blocks in the table
dba_tables.EMPTY_BLOCKS Number of empty (never used) data blocks in the table
dba_segments.blocks Size, in Oracle blocks, of the segment
∴dba_segments.blocks =dba_tables.BLOCKS +dba_tables.EMPTY_BLOCKS
在此也做了实验去证明:
我实践的结果很正常就是dba_segments.blocks =dba_tables.BLOCKS +dba_tables.EMPTY_BLOCKS
实践过程:
create table t1 as select * from user_objects;
查看user_tables.blocks和empty_blocks
SQL> SELECT a.TABLE_NAME,
2 a.INITIAL_EXTENT,
3 a.MIN_EXTENTS,
4 a.BLOCKS,
5 a.EMPTY_BLOCKS
6 FROM USER_TABLES A
7 WHERE A.TABLE_NAME = 'T1';
TABLE_NAME INITIAL_EXTENT MIN_EXTENTS BLOCKS EMPTY_BLOCKS
------------------------------ -------------- ----------- ---------- ------------
T1 65536 1 --->没有进行收集∴users_tables才不会显示 就例如user_tables.num_rows和真是的行数不一是一个意思
SQL>
查看user_segments.blocks
SQL> SELECT a.segment_name,
2 a.BLOCKS
3 FROM user_segments A
4 WHERE A.segment_name = 'T1'
5 ;
SEGMENT_NAME BLOCKS
--------------------------------------------------------------------------------- ----------
T1 8
分析下表
SQL> analyze table t1 compute statistics;
Table analyzed.
SQL> SELECT a.TABLE_NAME,
2 a.INITIAL_EXTENT,
3 a.MIN_EXTENTS,
4 a.BLOCKS,
5 a.EMPTY_BLOCKS
6 FROM USER_TABLES A
7 WHERE A.TABLE_NAME = 'T1';
TABLE_NAME INITIAL_EXTENT MIN_EXTENTS BLOCKS EMPTY_BLOCKS
------------------------------ -------------- ----------- ---------- ------------
T1 65536 1 4 4
SQL> SELECT a.segment_name,
2 a.BLOCKS
3 FROM user_segments A
4 WHERE A.segment_name = 'T1'
5 ;
SEGMENT_NAME BLOCKS
--------------------------------------------------------------------------------- ----------
T1 8
发现dba_segments.blocks =dba_tables.BLOCKS +dba_tables.EMPTY_BLOCKS
SQL> insert into t1 select * from t1;
90 rows created.
SQL> insert into t1 select * from t1;
180 rows created.
SQL> insert into t1 select * from t1;
360 rows created.
SQL> insert into t1 select * from t1;
720 rows created.
SQL> commit;
Commit complete.
SQL> SELECT a.TABLE_NAME,
2 a.INITIAL_EXTENT,
3 a.MIN_EXTENTS,
4 a.BLOCKS,
5 a.EMPTY_BLOCKS
6 FROM USER_TABLES A
7 WHERE A.TABLE_NAME = 'T1';
TABLE_NAME INITIAL_EXTENT MIN_EXTENTS BLOCKS EMPTY_BLOCKS
------------------------------ -------------- ----------- ---------- ------------
T1 65536 1 8 0
SQL> analyze table t1 compute statistics;
Table analyzed.
SQL> SELECT a.TABLE_NAME,
2 a.INITIAL_EXTENT,
3 a.MIN_EXTENTS,
4 a.BLOCKS,
5 a.EMPTY_BLOCKS
6 FROM USER_TABLES A
7 WHERE A.TABLE_NAME = 'T1';
TABLE_NAME INITIAL_EXTENT MIN_EXTENTS BLOCKS EMPTY_BLOCKS
------------------------------ -------------- ----------- ---------- ------------
T1 65536 1 23 1
SQL> SELECT a.segment_name,
2 a.BLOCKS
3 FROM user_segments A
4 WHERE A.segment_name = 'T1'
5 ;
SEGMENT_NAME BLOCKS
--------------------------------------------------------------------------------- ----------
T1 24
SQL> 24=23+1
您懂了么
如果理解的有问题 请及时告知 都学习和改进下 谢谢
( 建议都看官网的资料 )英语不好才要看奥
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28602568/viewspace-759540/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28602568/viewspace-759540/