普通表 分区表 大对象表之间不同的segment分配

scott@ORCL> create table table1 (id number,name varchar2(2));表已创建。scott@ORCL> create table table2 (id number,time date) partition by range(time)  2  ( partition p1 values less than (to_date('2010-10-1','yyyy-mm-dd')),  3  partition p2 values less than (to_date('2010-11-1','yyyy-mm-dd')),  4  partition p4 values less than (maxvalue)  5  );表已创建。scott@ORCL> create table table3 (id number,name clob);表已创建。--没有插入数据，oracle未给表分配空间scott@ORCL> select count(*) from user_segments where segment_name ='TABLE1';  COUNT(*)----------         0scott@ORCL> col segment_name for a20;--分区表虽未插入数据，但已分配空间scott@ORCL> select segment_name,partition_name from user_segments where segment_name ='TABLE2';SEGMENT_NAME         PARTITION_NAME-------------------- ------------------------------TABLE2               P1TABLE2               P2TABLE2               P4--大对象字段也未分配空间scott@ORCL> select segment_name,partition_name from user_segments where segment_name ='TABLE3';未选定行scott@ORCL> insert into table1 values(1,'a');已创建 1 行。--普通表插入数据之后分配空间scott@ORCL> select count(*) from user_segments where segment_name ='TABLE1';  COUNT(*)----------         1scott@ORCL> insert into table3 values(1,'a');已创建 1 行。--大对象表也是在插入数据之后分配空间scott@ORCL> select segment_name,partition_name from user_segments where segment_name ='TABLE3';SEGMENT_NAME         PARTITION_NAME-------------------- ------------------------------TABLE3---查看大数据表分配多少个segmentscott@ORCL> select count(*) from dba_segments;  COUNT(*)----------     13104scott@ORCL> drop table table3 purge;表已删除。scott@ORCL> select count(*) from dba_segments;  COUNT(*)----------     13101     --发现大数据对象表分配了三个segment，仔细对比创建表之后，分配了哪些segmentscott@ORCL> create table user_segment1 as select * from user_segments;表已创建。scott@ORCL> create table table3 (id number,name clob);表已创建。scott@ORCL> insert into table3 values(1,'a');已创建 1 行。scott@ORCL> col segment_name for a35;--大数据表分配了三个segment--表本身的segment--大对象的segment--LOBINDEX是为定位LOB字段内容自动创建的索引，无法删除,Oracle会自动维护。scott@ORCL> select segment_name,segment_type from user_segments where segment_name not in(select segment_name from user_segment1);SEGMENT_NAME                        SEGMENT_TYPE----------------------------------- ------------------SYS_IL0000089578C00002$$            LOBINDEXTABLE3                              TABLESYS_LOB0000089578C00002$$           LOBSEGMENTUSER_SEGMENT1                       TABLE