What's the difference between a dba_tables and a dba_segments table block?

Oracle Table Blocks Test

To create a test table:

Sql> CREATE TABLE t5 (id int);

Table has been created.

Sql> INSERT INTO T5 values (1);

1 lines have been created.

Sql> INSERT INTO T5 values (2);

1 lines have been created.

Sql> INSERT INTO T5 values (3);

1 lines have been created.

Sql> INSERT INTO T5 values (4);

1 lines have been created.

Sql> commit;

Submit completed.

Insert a little more data:

Sql> begin

2 for I in 1 1000 loop

3 INSERT into T5 values (10000);

4 commit;

5 end Loop;

6 end;

7/

Analysis table

sql> Analyze table T5 compute statistics;

The table is parsed.

Description

Why collect statistics because the blocks in Dba_tables is only a value after collecting statistics, and for empty_blocks parameters, you must use analyze analysis to have a value. If you use Dbms_stats.gather_table_stats collection, only blocks values can be collected and empty_blocks not be collected.

This column more highlights: http://www.bianceng.cn/database/Oracle/

To view the blocks values in the dba_segments and Dba_tables tables:

Sql> Select Segment_name,blocks,extents,bytes,segment_type,tablespace_name from

dba_segments where segment_name= ' T5 ';

Segme BLOCKS extents BYTES segment_type tablespace_name

----- ---------- ---------- ---------- ------------------ ----------------------

T5 6528 53477376 TABLE SYSTEM

Sql> Select Blocks,empty_blocks from dba_tables where table_name = ' T5 ';

BLOCKS Empty_blocks

----------   ------------

6449 78

From the results above, we can see that:

6528 = 6449+78 +1

That

dba_segments. Blocks = Dba_tables.blocks+dba_tables.empty_blocks +1 (segment header block)

This extra 1 is the segment header block.

If the result of the query is not the case, you may not have an analytic table. You might as well analyze the table and check it out.

The two tables also have different definitions for blocks:

Dba_segments. BLOCKS holds the total number of BLOCKS allocated to the table.

User_tables. BLOCKS holds the total number of BLOCKS allocated for data.