【Deferred Segment Creation】Oracle 11g新特性-延期建立段

Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONEOracle 11g內引入新特性：延期建立段。DDL語句執行後，只要此時段內沒有資料，並不立即建立段；等到第一次往段內新增資料時，才建立段。這樣可以減少一些儲存空間

 

注意事項：

1， 在sys使用者下建立的段立即建立，無論此時段內有無資料

A,建立在system表空間

scott@ORCL> conn / as sysdba

Connected.

sys@ORCL> show parameter defer

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

deferred_segment_creation            boolean     TRUE

sys@ORCL> create table t_test (n number);

 

Table created.

 

sys@ORCL> select * from user_segments where segment_name='T_TEST';

 

SEGMENT_NAME                   PARTITION_NAME                 SEGMENT_TYPE       SEGMENT_SU TABLESPACE_NAME

------------------------------ ------------------------------ ------------------ ---------- ------------------------------

     BYTES     BLOCKS    EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS   MAX_SIZE RETENTI MINRETENTION PCT_INCREASE

---------- ---------- ---------- -------------- ----------- ----------- ----------- ---------- ------- ------------ ------------

 FREELISTS FREELIST_GROUPS BUFFER_ FLASH_C CELL_FL

---------- --------------- ------- ------- -------

T_TEST                                                        TABLE              MSSM       SYSTEM

     65536          8          1          65536     1048576           1  2147483645 2147483645

         1               1 DEFAULT DEFAULT DEFAULT

 

 

sys@ORCL> drop table t_test;

 

Table dropped.

 

B,建立在非system表空間

scott@ORCL> conn / as sysdba

Connected.

sys@ORCL> create table t_test (n number) tablespace users;

 

Table created.

 

sys@ORCL> select * from user_segments where segment_name='T_TEST';

 

SEGMENT_NAME                   PARTITION_NAME                 SEGMENT_TYPE       SEGMENT_SU TABLESPACE_NAME

------------------------------ ------------------------------ ------------------ ---------- ------------------------------

     BYTES     BLOCKS    EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS   MAX_SIZE RETENTI MINRETENTION PCT_INCREASE

---------- ---------- ---------- -------------- ----------- ----------- ----------- ---------- ------- ------------ ------------

 FREELISTS FREELIST_GROUPS BUFFER_ FLASH_C CELL_FL

---------- --------------- ------- ------- -------

T_TEST                                                        TABLE              ASSM       USERS

     65536          8          1          65536     1048576           1  2147483645 2147483645

                           DEFAULT DEFAULT DEFAULT

 

2，在其它使用者下建立的段，只要此時段內沒有資料，並不立即建立段

A,建立在非system表空間

sys@ORCL> conn scott/tiger;

Connected.

scott@ORCL>  create table t_test (n number);

 

Table created.

 

 

scott@ORCL> select * from user_segments where segment_name='T_TEST';

 

no rows selected

 

scott@ORCL> insert into t_test values(1);

 

1 row created.

 

scott@ORCL> select * from user_segments where segment_name='T_TEST';

 

SEGMENT_NAME                   PARTITION_NAME                 SEGMENT_TYPE       SEGMENT_SU TABLESPACE_NAME

------------------------------ ------------------------------ ------------------ ---------- ------------------------------

     BYTES     BLOCKS    EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS   MAX_SIZE RETENTI MINRETENTION PCT_INCREASE

---------- ---------- ---------- -------------- ----------- ----------- ----------- ---------- ------- ------------ ------------

 FREELISTS FREELIST_GROUPS BUFFER_ FLASH_C CELL_FL

---------- --------------- ------- ------- -------

T_TEST                                                        TABLE              ASSM       USERS

     65536          8          1          65536     1048576           1  2147483645 2147483645

                           DEFAULT DEFAULT DEFAULT

 

 

scott@ORCL> drop table t_test;

 

Table dropped.

 

B,建立在system表空間

scott@ORCL> create table t_test (n number) tablespace system;

 

Table created.

 

scott@ORCL> select * from user_segments where segment_name='T_TEST';

 

no rows selected

 

scott@ORCL> insert into t_test values(1);

 

1 row created.

 

scott@ORCL> select * from user_segments where segment_name='T_TEST';

 

SEGMENT_NAME                   PARTITION_NAME                 SEGMENT_TYPE       SEGMENT_SU TABLESPACE_NAME

------------------------------ ------------------------------ ------------------ ---------- ------------------------------

     BYTES     BLOCKS    EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS   MAX_SIZE RETENTI MINRETENTION PCT_INCREASE

---------- ---------- ---------- -------------- ----------- ----------- ----------- ---------- ------- ------------ ------------

 FREELISTS FREELIST_GROUPS BUFFER_ FLASH_C CELL_FL

---------- --------------- ------- ------- -------

T_TEST                                                        TABLE              MSSM       SYSTEM

     65536          8          1          65536     1048576           1  2147483645 2147483645

         1               1 DEFAULT DEFAULT DEFAULT

 

 

scott@ORCL> drop table t_test;

 

Table dropped.

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/14359/viewspace-735915/，如需轉載，請註明出處，否則將追究法律責任。