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

Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE Oracle 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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14359/viewspace-735915/