ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【Deferred Segment Creation】Oracle 11g新特性-延期创建段

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

原创 Linux操作系统 作者:landf 时间:2012-07-18 11:48:03 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-02-14

  • 博文量
    68
  • 访问量
    488146