一、結論 dba_segments指定表名查詢到的段大小包含索引、約束、表欄位數據（包含LOB欄位）（1）表（不包含LOB欄位）創建預設分配2個簇，1個簇用於存放表結構及欄位數據，1個簇用於存放cluster類型的索引（2）創建索引預設會分配2個簇，用於存放normal索引數據（3）創建約束不會額外 ...

一、結論

dba_segments指定表名查詢到的段大小包含索引、約束、表欄位數據（包含LOB欄位）
（1）表（不包含LOB欄位）創建預設分配2個簇，1個簇用於存放表結構及欄位數據，1個簇用於存放cluster類型的索引
（2）創建索引預設會分配2個簇，用於存放normal索引數據
（3）創建約束不會額外分配空間，除非是主鍵或唯一約束
（4）添加LOB欄位預設分配2個簇，用於存放lob欄位數據
（5）表（包含LOB欄位）創建預設分配4個簇，1個簇用於存放表結構及欄位數據，1個簇用於存放cluster類型的索引，2和簇用於存放lob欄位數據

二、測試過程

（一）創建測試表，查看初始大小

create table test01(id number,name varchar2(10));

SQL> select owner,segment_name,bytes,blocks,extents,initial_extent from dba_segments where owner='SYSDBA' and segment_name='TEST01';

OWNER  SEGMENT_NAME BYTES                BLOCKS               EXTENTS              INITIAL_EXTENT      
------ ------------ -------------------- -------------------- -------------------- --------------------
SYSDBA TEST01       2097152              64                   2                    1048576

SQL> select owner,index_name,index_type,initial_extent from dba_indexes where owner='SYSDBA' and table_name='TEST01';

OWNER  INDEX_NAME    INDEX_TYPE INITIAL_EXTENT
------ ------------- ---------- --------------
SYSDBA INDEX33566761 CLUSTER    1

SQL> select owner,segment_name,bytes,blocks,extents,initial_extent from dba_segments where owner='SYSDBA' and segment_name in('INDEX33566761');
未選定行

（二）添加索引，查看空間變化

create index idx_test01_name on test01(name);

SQL> select owner,segment_name,bytes,blocks,extents,initial_extent from dba_segments where owner='SYSDBA' and segment_name='TEST01';

OWNER SEGMENT_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT
------ ------------ -------------------- -------------------- -------------------- --------------------
SYSDBA TEST01 4194304 128 4 1048576

SQL> select owner,index_name,index_type,initial_extent from dba_indexes where owner='SYSDBA' and table_name='TEST01';

OWNER INDEX_NAME INDEX_TYPE INITIAL_EXTENT
------ --------------- ---------- --------------
SYSDBA INDEX33566761 CLUSTER 1
SYSDBA IDX_TEST01_NAME NORMAL 1

SQL> select owner,segment_name,bytes,blocks,extents,initial_extent from dba_segments where owner='SYSDBA' and segment_name in('INDEX33566761','IDX_TEST01_NAME');

OWNER SEGMENT_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT
------ --------------- -------------------- -------------------- -------------------- --------------------
SYSDBA IDX_TEST01_NAME 2097152 64 2 1048576

（三）添加主鍵，查看空間變化

alter table test01 add constraint pk_test01_id primary key(id);

SQL> select owner,segment_name,bytes,blocks,extents,initial_extent from dba_segments where owner='SYSDBA' and segment_name='TEST01';

OWNER SEGMENT_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT
------ ------------ -------------------- -------------------- -------------------- --------------------
SYSDBA TEST01 6291456 192 6 1048576

SQL> select owner,index_name,index_type,initial_extent from dba_indexes where owner='SYSDBA' and table_name='TEST01';

OWNER INDEX_NAME INDEX_TYPE INITIAL_EXTENT
------ --------------- ---------- --------------
SYSDBA INDEX33566761 CLUSTER 1
SYSDBA INDEX33566763 NORMAL 1
SYSDBA IDX_TEST01_NAME NORMAL 1

SQL> select constraint_name,constraint_type,status,index_name from dba_constraints where owner='SYSDBA' and table_name='TEST01';

CONSTRAINT_NAME CONSTRAINT_TYPE STATUS INDEX_NAME
--------------- --------------- ------- -------------
PK_TEST01_ID P ENABLED INDEX33566763

SQL> select owner,segment_name,bytes,blocks,extents,initial_extent from dba_segments where owner='SYSDBA' and segment_name in('INDEX33566761','IDX_TEST01_NAME','INDEX33566763');

OWNER SEGMENT_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT
------ --------------- -------------------- -------------------- -------------------- --------------------
SYSDBA IDX_TEST01_NAME 2097152 64 2 1048576
SYSDBA INDEX33566763 2097152 64 2 1048576

（四）創建約束，查看空間變化

alter table test01 add constraint ck_test01_id check(id>=1);

SQL> select owner,segment_name,bytes,blocks,extents,initial_extent from dba_segments where owner='SYSDBA' and segment_name='TEST01';

OWNER SEGMENT_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT
------ ------------ -------------------- -------------------- -------------------- --------------------
SYSDBA TEST01 6291456 192 6 1048576

SQL> select constraint_name,constraint_type,status,index_name from dba_constraints where owner='SYSDBA' and table_name='TEST01';

CONSTRAINT_NAME CONSTRAINT_TYPE STATUS INDEX_NAME
--------------- --------------- ------- -------------
CK_TEST01_ID C ENABLED NULL
PK_TEST01_ID P ENABLED INDEX33566763

（五）增加lob列和普通列，查看空間變化

alter table test01 add content clob;

SQL> select owner,segment_name,bytes,blocks,extents,initial_extent from dba_segments where owner='SYSDBA' and segment_name='TEST01';

OWNER SEGMENT_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT
------ ------------ -------------------- -------------------- -------------------- --------------------
SYSDBA TEST01 8388608 256 8 1048576

alter table test01 add city varchar2(10);

SQL> select owner,segment_name,bytes,blocks,extents,initial_extent from dba_segments where owner='SYSDBA' and segment_name='TEST01';

OWNER SEGMENT_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT
------ ------------ -------------------- -------------------- -------------------- --------------------
SYSDBA TEST01 8388608 256 8 1048576


SQL> select id,name,crtdate from sysobjects where name='TEST01' and schid in(select id from sysobjects where name='SYSDBA' and type$='SCH');

ID NAME CRTDATE
----------- ------ --------------------------
5751 TEST01 2022-02-22 14:55:03.347000

SQL> SELECT TABLE_USED_LOB_PAGES('SYSDBA','TEST01');

TABLE_USED_LOB_PAGES('SYSDBA','TEST01')
---------------------------------------
6 ---這個返回的是數據塊，2個簇實際在用的數據塊數量只有6個

（六）創建包含LOB欄位的表

SQL> create table test02(id number,name varchar2(10),content clob);
SQL> select owner,segment_name,bytes,blocks,extents,initial_extent from dba_segments where owner='SYSDBA' and segment_name='TEST02';

OWNER SEGMENT_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT
------ ------------ -------------------- -------------------- -------------------- --------------------
SYSDBA TEST02 4194304 128 4 1048576