自oracle11g开始,提供延迟段创建功能,即创建表(未插入实际数据)是否马上为其分配segment
 
 -----测试场景
  SQL> select * from v$version;
 
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

-----创建表
SQL> create table t_segment(a int);
 
Table created
 
SQL> desc user_segments;
Name             Type         Nullable Default Comments                                                                                                                            
---------------- ------------ -------- ------- ------------------------------------------------------------------------------------------------------------------------------------
SEGMENT_NAME     VARCHAR2(81) Y                Name, if any, of the segment                                                                                                        
PARTITION_NAME   VARCHAR2(30) Y                Partition/Subpartition Name, if any, of the segment                                                                                 
SEGMENT_TYPE     VARCHAR2(18) Y                Type of segment:  "TABLE", "CLUSTER", "INDEX", "ROLLBACK", "DEFERRED ROLLBACK", "TEMPORARY", "SPACE HEADER", "TYPE2 UNDO" or "CACHE"
SEGMENT_SUBTYPE  VARCHAR2(10) Y                SubType of Lob segment:  "SECUREFILE", "ASSM", "MSSM", NULL                                                                         
TABLESPACE_NAME  VARCHAR2(30) Y                Name of the tablespace containing the segment                                                                                       
BYTES            NUMBER       Y                Size, in bytes, of the segment                                                                                                      
BLOCKS           NUMBER       Y                Size, in Oracle blocks, of the segment                                                                                              
EXTENTS          NUMBER       Y                Number of extents allocated to the segment                                                                                          
INITIAL_EXTENT   NUMBER       Y                Size, in bytes, of the initial extent of the segment                                                                                
NEXT_EXTENT      NUMBER       Y                Size, in bytes, of the next extent to be allocated to the segment                                                                   
MIN_EXTENTS      NUMBER       Y                Minimum number of extents allowed in the segment                                                                                    
MAX_EXTENTS      NUMBER       Y                Maximum number of extents allowed in the segment                                                                                    
MAX_SIZE         NUMBER       Y                Maximum number of blocks allowed in the segment                                                                                     
RETENTION        VARCHAR2(7)  Y                Retention option for SECUREFILE segment                                                                                             
MINRETENTION     NUMBER       Y                Minimum Retention Duration for SECUREFILE segment                                                                                   
PCT_INCREASE     NUMBER       Y                Percent by which to increase the size of the next extent to be allocated                                                            
FREELISTS        NUMBER       Y                Number of process freelists allocated to this segment                                                                               
FREELIST_GROUPS  NUMBER       Y                Number of freelist groups allocated to this segment                                                                                 
BUFFER_POOL      VARCHAR2(7)  Y                The default buffer pool to be used for blocks from this segment                                                                     
FLASH_CACHE      VARCHAR2(7)  Y                                                                                                                                                    
CELL_FLASH_CACHE VARCHAR2(7)  Y                                                                                                                                                    
 
 
--oracle默认开启
SQL> show parameter defer
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE

--未插入记录,不分配segment
SQL> select segment_name,initial_extent,tablespace_name from user_segments where segment_name='T_SEGMENT';
 
SEGMENT_NAME                                                                     INITIAL_EXTENT TABLESPACE_NAME
-------------------------------------------------------------------------------- -------------- ------------------------------

 
SQL> insert into t_segment values(1);
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
--插入记录后分配segment
SQL> select segment_name,initial_extent,tablespace_name from user_segments where segment_name='T_SEGMENT';
 
SEGMENT_NAME                                                                     INITIAL_EXTENT TABLESPACE_NAME
-------------------------------------------------------------------------------- -------------- ------------------------------
T_SEGMENT                                                                                 65536 TBS_HANG

---配置为false情形又如何
SQL> alter session set   deferred_segment_creation=false;
 
Session altered
 
SQL> drop table t_segment purge;
 
Table dropped
 
SQL> create table t_segment(a int);
 
Table created

---未插入记录也分配segment
SQL> select segment_name,initial_extent,tablespace_name from user_segments where segment_name='T_SEGMENT';
 
SEGMENT_NAME                                                                     INITIAL_EXTENT TABLESPACE_NAME
-------------------------------------------------------------------------------- -------------- ------------------------------
T_SEGMENT                                                                                 65536 TBS_HANG

 

SQL> show parameter def
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     FALSE
 
SQL> drop table t_segment purge;
 
Table dropped
 
---表级指定延迟段分配
SQL> create table t_segment(a int) segment creation deferred;
 
Table created
 
---未分配segment,虽然参数deferred_segment_creation为false,说明表级优先于参数配置
SQL> select segment_name,initial_extent,tablespace_name from user_segments where segment_name='T_SEGMENT';
 
SEGMENT_NAME                                                                     INITIAL_EXTENT TABLESPACE_NAME
-------------------------------------------------------------------------------- -------------- ------------------------------

--如下不再文字描述,贴测试结果
SQL> drop table t_segment purge;
 
Table dropped
 
SQL> create table t_segment(a int) segment creation immediate;
 
Table created
 
SQL> select segment_name,initial_extent,tablespace_name from user_segments where segment_name='T_SEGMENT';
 
SEGMENT_NAME                                                                     INITIAL_EXTENT TABLESPACE_NAME
-------------------------------------------------------------------------------- -------------- ------------------------------
T_SEGMENT                                                                                 65536 TBS_HANG
 
SQL>