【DB笔试面试274】在Oracle中，什么是延迟段创建（Deferred Segment Creation）？

小麦苗DBA宝典

发布于 2022-02-22 14:27:27

7280

发布于 2022-02-22 14:27:27

文章被收录于专栏：小麦苗的DB宝专栏小麦苗的DB宝专栏

题目如下所示：

在Oracle中，什么是延迟段创建（Deferred Segment Creation）？

答案如下所示：

在Oracle 11.2中，当创建一个空表或者空分区时，为了加快创建速度，Oracle并不会立即分配初始段和空间，实际的表段（Table Segement）被延迟到第一行数据插入时创建。延迟段创建特性通过DEFERRED_SEGMENT_CREATION参数控制，默认为TRUE，表示开启该功能。延迟段创建可以节省空间，加快初始化过程，是面向性能和资源的一个优化。可以通过修改参数DEFERRED_SEGMENT_CREATION来关闭这个特性：

ALTER SYSTEM SET DEFERRED_SEGMENT_CREATION=FLASE;

该参数为动态参数，不需要重启数据库。可以通过如下的SQL语句找到所有的空表（未分配任何区的表）：

SELECT SEGMENT_CREATED,TABLE_NAME FROM USER_TABLES WHERE SEGMENT_CREATED = 'NO';

延迟段创建的限制条件有：

①　延迟段创建不支持的表类型包括：索引组织表（Index-Organized Tables）、簇表（Clustered Tables）、全局临时表（Global Temporary Tables）、会话级临时表（Session-Specific Temporary Tables）、内部表（Internal Tables）、Typed表（Typed Tables）、AQ表（AQ Tables）和外部表（External Tables）。

②　用户SYS、SYSTEM、PUBLIC、OUTLN和XDB下的表不支持延迟段创建。

③　延迟段创建不支持位图连接索引（Bitmap Join Indexes）和域索引（Domain Indexes）。

④　延迟段创建不支持字典管理表空间（Dictionary-Managed Tablespace，DMT）。

⑤　延迟段创建不支持SYSTEM表空间。

⑥　延迟段创建从Oracle 11.2.0.2版本开始才开始支持分区和子分区。

在创建表的时候，当SEGEMENT CREATION为IMMEDIATE时，Oracle会为表建立段（SEGMENT），当SEGEMENT CREATION为DEFERRED时，Oracle不会为空表建立段，如下所示：

SQL> CREATE TABLE T_TEST_2(ID NUMBER,NAME VARCHAR2(10)) SEGMENT CREATION IMMEDIATE;
表已创建。
SQL> CREATE TABLE T_TEST_3(ID NUMBER,NAME VARCHAR2(10)) SEGMENT CREATION DEFERRED;
表已创建。
SQL> SELECT SEGMENT_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME LIKE 'T_TEST%';
SEGMENT_NAME
-------------
T_TEST_2
SQL> INSERT INTO T_TEST_1 VALUES(1,'LHRDB');
已创建 1 行。
SQL> SELECT SEGMENT_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME LIKE 'T_TEST%';
SEGMENT_NAME
------------
T_TEST_1
T_TEST_2

可以看到，在SEGEMENT CREATION为IMMEDIATE的情况下，Oracle为T_TEST_2建立了段，在SEGEMENT CREATION为DEFERRED的情况下，Oracle没有为表T_TEST_3建立段，当向没有分配段的空表中插入信息时，Oracle会自动为空表建立段。需要注意的是，未分配任何区（EXTENT）的表，在DBA_SEGMENTS视图中是查不到的。若使用不加DROP ALL STROAGE的TRUNCATE语句，则分配的段只是缩小到最初分配的大小，并没有完全回收段，如下所示：

SQL> TRUNCATE TABLE TEST1;          ------使用不加子句的TRUNCATE
Table truncated.
SQL> SELECT SEGMENT_NAME,BYTES FROM USER_SEGMENTS;       -----只是删除到最小的区
SEGMENT_NAME                        BYTES
------------------------------ ----------
TEST1                               65536
SQL> TRUNCATE TABLE TEST1 DROP ALL STORAGE;         -----使用11g中新加的子句DROP ALL STROAGE
Table truncated.
SQL> SELECT SEGMENT_NAME,BYTES FROM USER_SEGMENTS;      -----段全部被回收
no rows selected

需要关注的是，这个新特性会带来一个问题：在使用exp/imp进行导出导入时，不会包含这些空表（数据泵expdp不存在这个问题，expdp可以导出空表），所以，在迁移数据的时候可能会导致遗漏部分空表。针对这个问题的解决方法有：

①　最原始最笨的办法（不推荐）：INSERT一行，再ROLLBACK或者删除就可以产生SEGMENT了。该方法是在空表中插入数据，再删除，则会产生SEGMENT，此时再导出时就可以导出空表。

②　设置DEFERRED_SEGMENT_CREATION参数。设置DEFERRED_SEGMENT_CREATION参数为FALSE来禁用“延迟段创建”，无论是空表还是非空表，都会分配SEGMENT。需要注意的是，该值设置后只对后面新增的表产生作用，对之前建立的空表（已经存在的）不起作用，仍不能导出。

③　使用ALLOCATE EXTENT。使用ALLOCATE EXTENT可以为数据库的每一张表分配EXTENT。批量生成脚本：

SELECT 'ALTER TABLE '||D.OWNER||'.'||D.TABLE_NAME||' ALLOCATE EXTENT;' EXEC_SQL FROM DBA_TABLES D WHERE D.SEGMENT_CREATED='NO' AND D.OWNER IN ('LHR','ABC'); --注意修改用户名

执行以上SQL产生的脚本后即可为每一个空表分配段，然后执行exp命令即可。

总结一下，当数据库服务器的版本为11.2.0.1或使用exp 11.2.0.1工具进行导出的时候，需要注意空表的问题。由于延迟段创建的特性，会导致空表不能正常导出。从Oracle 11.2.0.2开始，已经不存在该问题了。

在数据库服务器版本为Oracle 11.2.0.1中，当采用exp（无论版本为多少）导出数据时，需要先判断数据库中是否含有空表。因为若基于SCHEMA级别或FULL级别导出数据时不会报错，所以，可能会漏掉一些空表定义。当数据库中含有空表时，需要先设置参数DEFERRED_SEGMENT_CREATION为FALSE，然后使用ALLOCATE EXTENT为每一个空表分配段，最后再导出所需要的数据。另外，对于分区表而言，即使没有创建段（无论是整个分区还是个别子分区没有创建段，可以使用DBA_TAB_PARTITIONS.SEGMENT_CREATED来查询是否创建了段），也不存在丢失分区表或丢失某个子分区的问题。

需要注意以下几点：

①　当exp的版本为11.2.0.1时，无论数据库服务器的版本为多少，都不能正常导出空表。所以，建议不要使用版本为11.2.0.1的exp工具导出数据。

②　当数据库服务器的版本为11.2.0.1时（无论exp的版本为多少），基于SCHEMA级别或FULL级别导出数据时都不会报错，其它情况均会报错。当数据库服务器版本为11.2.0.1时，则会报错类似于：“EXP-00011: LHR.TESTDSC does not exist”。当exp的版本为11.2.0.1时，则会报错类似于：“EXP-00003: 未找到段 (0,0) 的存储定义”。

& 说明：

有关Oracle的延迟段创建的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2143238/

DB笔试面试历史连接