DEFERRED_SEGMENT_CREATION
Property | Description |
|---|---|
Parameter type | Boolean |
Default value | true |
Modifiable | ALTER SESSION, ALTER SYSTEM |
Range of values | true | false |
Basic | No |
DEFERRED_SEGMENT_CREATION specifies the semantics of deferred segment creation. If set to true, then segments for non-partitioned tables and their dependent objects (LOBs, indexes) will not be created until the first row is inserted into the table.
Before creating a set of tables, if it is known that a significant number of them will not be populated, then consider setting this parameter to true. This saves disk space and minimizes install time.
这个参数的作用是创建表的时候延迟创建这个表相关的segment(包括lobs,indexes),直到第一次插入数据的时候才创建segment.
该参数从11.2.0.1引进,默认值为true。
用我的环境看看效果：
OS:windows 7 64bit
DB:11.2.0.3
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL>
SQL>
SQL> show parameter deferred_segment_creation
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean TRUE
SQL>
SQL>
SQL>
SQL> create table scott.defer_test as select * from dba_objects where 1=0;
表已创建。
SQL> select * from scott.defer_test;
未选定行
SQL>
SQL> select owner,segment_name,bytes from dba_segments where segment_name='DEFER_TEST';
未选定行
SQL>
---未分配segment
再创建index看下结果
SQL> create index scott.idx_defer_id on scott.defer_test(object_id);
索引已创建。
SQL>
SQL> select owner,segment_name,bytes from dba_segments where segment_name in('DEFER_TEST','IDX_DEFER_ID');
未选定行
SQL>
---创建索引后仍然未分配segment
插入一条数据
SQL>
SQL> insert into scott.defer_test select * from dba_objects where rownum<2;
已创建 1 行。
SQL>
SQL>
SQL> commit; ----如果是rollback，同样会产生segment
提交完成。
SQL>set lines 200
SQL> col segment_name for a40
SQL> select owner,segment_name,bytes from dba_segments where segment_name in('DEFER_TEST','IDX_DEFER_ID');
OWNER SEGMENT_NAME BYTES
------------------------------ ---------------------------------------- ----------
SCOTT IDX_DEFER_ID 65536
SCOTT DEFER_TEST 65536
----插入一条数据后，分配segment
可以通过在创建表或索引的时候指定SEGMENT CREATION IMMEDIATE子句，在创建表的时候直接分配segment
或则通过修改DEFERRED_SEGMENT_CREATION参数为false，该参数可以在session level或则system level进行修改。
测试1：指定segment creation immediate子句
SQL>
SQL> create table scott.defer_test1 (tid number,tname varchar2(10)) segment creation immediate;
表已创建。
SQL>
SQL> select owner,segment_name,bytes from dba_segments where segment_name ='DEFER_TEST1';
OWNER SEGMENT_NAME BYTES
------------------------------ ---------------------------------------- ----------
SCOTT DEFER_TEST1 65536
SQL>
----直接分配了segment
测试2：修改DEFERRED_SEGMENT_CREATION参数
alter session set DEFERRED_SEGMENT_CREATION=false;
create table scott.defer_test2 (tid number,tname varchar2(10)) segment creation immediate;
select owner,segment_name,bytes from dba_segments where segment_name ='DEFER_TEST2';
SQL>
SQL> alter session set DEFERRED_SEGMENT_CREATION=false;
会话已更改。
SQL> create table scott.defer_test2 (tid number,tname varchar2(10)) segment creation immediate;
表已创建。
SQL> select owner,segment_name,bytes from dba_segments where segment_name ='DEFER_TEST2';
OWNER SEGMENT_NAME BYTES
------------------------------ ---------------------------------------- ----------
SCOTT DEFER_TEST2 65536
SQL>
----直接分配了segment
补充说明：
在11.2.0.2之前如果用exp导出未分配segment的表时会出现EXP-00011: 'Table Name' does not exist的提示
根据MOS文档In 11gR2 Skips The Table At Full And Schema Level Export Or Shows EXP-00011 At Table Level Export [ID 1178343.1]提供的方案：
1、alter table DEFERRED_SEGMENT.DEF_TAB move;
2、alter table deferred_segment.DEF_TAB allocate extent;
其实原理就是分配segment，该问题在11.2.0.2中得到了解决，建议使用expdp来进行操作。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
