DBA_SEGMENTS 資料字典 塊數量和發生變化情況

Oracle Database Reference

10g Release 2 (10.2)

http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_4097.htm

DBA_SEGMENTS

DBA_SEGMENTS describes the storage allocated for all segments in the database.

Related View

USER_SEGMENTS describes the storage allocated for the segments owned by the current user's objects. This view does not display the OWNER, HEADER_FILE, HEADER_BLOCK, or RELATIVE_FNO columns.

Column	Datatype	NULL	Description
OWNER	VARCHAR2(30)		Username of the segment owner
SEGMENT_NAME	VARCHAR2(81)		Name, if any, of the segment
PARTITION_NAME	VARCHAR2(30)		Object Partition Name (Set to NULL for non-partitioned objects)
SEGMENT_TYPE	VARCHAR2(18)		Type of segment: INDEX PARTITION, TABLE PARTITION, TABLE, CLUSTER, INDEX, ROLLBACK, DEFERRED ROLLBACK, TEMPORARY, CACHE, LOBSEGMENT and LOBINDEX
TABLESPACE_NAME	VARCHAR2(30)		Name of the tablespace containing the segment
HEADER_FILE	NUMBER		ID of the file containing the segment header
HEADER_BLOCK	NUMBER		ID of the block containing the segment header
BYTES	NUMBER		Size, in bytes, of the segment
BLOCKS	NUMBER		Size, in Oracle blocks, of the segment
EXTENTS	NUMBER		Number of extents allocated to the segment
INITIAL_EXTENT	NUMBER		Size in bytes requested for the initial extent of the segment at create time. (Oracle rounds the extent size to multiples of 5 blocks if the requested size is greater than 5 blocks.)
NEXT_EXTENT	NUMBER		Size in bytes of the next extent to be allocated to the segment
MIN_EXTENTS	NUMBER		Minimum number of extents allowed in the segment
MAX_EXTENTS	NUMBER		Maximum number of extents allowed in the segment
PCT_INCREASE	NUMBER		Percent by which to increase the size of the next extent to be allocated
FREELISTS	NUMBER		Number of process freelists allocated to this segment
FREELIST_GROUPS	NUMBER		Number of freelist groups allocated to this segment
RELATIVE_FNO	NUMBER		Relative file number of the segment header
BUFFER_POOL	VARCHAR2(7)		Default buffer pool for the object

我查看下錶:

SELECT * from dba_segments where segment_name like 'REP_COMMON_STAT'

OWNER           ZMAS

SEGMENT_NAME    REP_COMMON_STAT

PARTITION_NAME  

SEGMENT_TYPE    TABLE

TABLESPACE_NAME ZMAS_DATA

HEADER_FILE     56

HEADER_BLOCK    449579

BYTES           6291456

BLOCKS          768

EXTENTS         21

INITIAL_EXTENT  65536

NEXT_EXTENT    

MIN_EXTENTS     1

MAX_EXTENTS     2147483645

PCT_INCREASE    

FREELISTS    

FREELIST_GROUPS    

RELATIVE_FNO    6

BUFFER_POOL     DEFAULT

HEADER_FILE 表示在哪個ID的資料檔案裡;

HEADER_BLOCK 表示段頭塊的ID號  可別認為是段的整個塊數了;

分析下表

analyze table REP_COMMON_STAT compute statistics;

說明：

         為什麼要收集統計資訊，因為dba_tables 中的blocks 是只有收集統計資訊以後才有值，而且對於empty_blocks 參數，還必須使用analyze 分析之後才有值。 如果使用dbms_stats.gather_table_stats收集，只能收集到blocks的值，empty_blocks 收集不到。

BACKED_UP    N

NUM_ROWS    58

BLOCKS      748

EMPTY_BLOCKS  19

AVG_SPACE     0

CHAIN_CNT    0

AVG_ROW_LEN    71

Dba_Segments .blocks = Dba_Tables.Blocks+Dba_Tables.Empty_Blocks +1（segment header block）

這個多加的1是，是segment header block. 

如果查詢的結果不是這樣，可能是你沒有分析表。 不妨分析表之後在查一下看看。 

這兩張表對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.

刪除表裡的所有資料

delete rep_common_stat;

兩個資料表裡的資訊不發生變更

trncate table rep_common_stat;

資料區段的資訊發生變化,資料表資訊不發生變化.

BYTES        65536
BLOCKS    8
EXTENTS   1