自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>

create table進階學習(三)

相關文章