Oracle 11gR2新特性--延迟段创建（Deferred Segment Creation）和exp不能导出空表

2022-06-01 04:24:19

真题1、什么是延迟段创建（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可以导出空表），所以，在迁移数据的时候可能会导致遗漏部分空表。针对这个问题的解决方法有：

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

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

3、使用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) 的存储定义”。

The new feature for the space allocation method in 11gR2 causes the export utility to skip the table in case of full or schema level exports. Using a table level export it displays "EXP-00011: 'Table Name' does not exist" in the export log.

show parameter deferred_segment_creation

create table testdsc(id number);

SELECT D.SEGMENT_CREATED FROM dba_tables d WHERE d.TABLE_NAME='TESTDSC';

SELECT * FROM DBA_SEGMENTS D WHERE D.segment_name='TESTDSC';

exp lhr/lhr file=a.dmp tables=TESTDSC

exp lhr/lhr@192.168.59.130/ora11g file=a.dmp tables=TESTDSC

exp lhr/lhr@192.168.1.31/orastrac file=a.dmp tables=TESTDSC

exp lhr/lhr@192.168.59.10/DB11202 file=a.dmp tables=TESTDSC

基于SCHEMA级别或FULL级别导出，不会报错，但是基于表级别导出会报错：

客户端为：11.2.0.3、11.2.0.1、11.2.0.2

服务器为：11.2.0.1

About to export specified tables via Conventional Path ...

EXP-00011: LHR.TESTDSC does not exist

Export terminated successfully with warnings.

客户端为：11.2.0.1

服务器：11.2.0.3、11.2.0.2

即将导出指定的表通过常规路径...

. . 正在导出表 TESTDSC

EXP-00003: 未找到段 (0,0) 的存储定义

导出成功终止, 但出现警告。

http://docs.oracle.com/cd/E11882_01/server.112/e22490/whatsnew.htm#SUTIL2871

Original Export

In Oracle Database 11g release 2 (11.2), the DEFERRED_SEGMENT_CREATION parameter is set to TRUE by default. This means that any tables you create do not have any segments until the first row of data is inserted into the table. Original Export ignores tables without segments. Therefore, if you create any new tables and do not insert any data before performing an export, those tables are not exported. (Note that Data Pump Export does not have this limitation because it does not ignore tables without segments.)

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 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.

Restrictions on Deferred Segment Creation This clause is subject to the following restrictions:

You cannot defer segment creation for the following types of tables: index-organized tables, clustered tables, global temporary tables, session-specific temporary tables, internal tables, typed tables, AQ tables, external tables, and tables owned by SYS, SYSTEM, PUBLIC, OUTLN, or XDB.
Deferred segment creation is supported on partitions and subpartitions beginning with Oracle Database 11g Release 2 (11.2.0.2).
Deferred segment creation is not supported for bitmap join indexes and domain indexes.
Deferred segment creation is not supported in dictionary-managed tablespaces.
Deferred segment creation is not supported in the SYSTEM tablespace.
Serializable transactions do not work with deferred segment creation. Trying to insert data into an empty table with no segment created causes an error.

ORACLE 11g 用exp命令导出库文件备份时，发现只能导出来一部分表而且不提示错误，之前找不到解决方案只能把没导出来的表重新建建立。后来发现是所有的空表都没有导出来。于是想好好查查,因为在以前的10g版本中没有这样的问题。

查资料发现Oracle 11g中有个新特性：新增了一个参数“deferred_segment_creation”含义是段延迟创建，默认是true。

具体是什么意思呢？

如果这个参数设置为true，你新建了一个表Table1，并且没有向其中插入数据，那么这个表不会立即分配extent，也就是不占数据空间，即表也不分配 segment 以节省空间，所以这些表也没能导出来。在系统表user_tables中也可以看到segment_treated的字段里是“NO”或者“YES”说明了某张表是否分配了segment。说白了是为了可以节省少量的空间。

用下面的SQL语句查询，可以发现没有导出的表其 segment_created 字段值都是 'NO'。

Select segment_created,table_name from user_tables where segment_created = 'NO';

解决方法：

1、最原始最笨的办法(不推荐):insert一行，再rollback或者删除就产生segment了。

该方法是在在空表中插入数据，再删除，则产生segment。导出时则可导出空表。

2、设置deferred_segment_creation 参数：

设置deferred_segment_creation 参数为FALSE来禁用"段推迟创建"(也就是直接创建segment)，无论是空表还是非空表，都分配segment。

在sqlplus中，执行如下命令：

SQL>alter system set deferred_segment_creation=false;

查看：

SQL>show parameter deferred_segment_creation;

注意：该值设置后只对后面新增的表产生作用，对之前建立的空表(已经存在的)不起作用，仍不能导出。

并且要重新启动数据库，让参数生效。

3、使用ALLOCATE EXTENT，可以导出之前已经存在的空表。

使用ALLOCATE EXTENT可以为数据库对象的每一张表分配Extent(注意针对每一张表，就是说一张表需要一条SQL代码)：

其语法如下：

-----------

ALLOCATE EXTENT { SIZE integer [K | M] | DATAFILE 'filename' | INSTANCE integer }

-----------

可以针对数据表、索引、物化视图等手工分配Extent。

ALLOCATE EXTENT使用样例:

ALLOCATE EXTENT

ALLOCATE EXTENT(SIZE integer [K | M])

ALLOCATE EXTENT(DATAFILE 'filename')

ALLOCATE EXTENT(INSTANCE integer) www.2cto.com

ALLOCATE EXTENT(SIZE integer [K | M] DATAFILE 'filename')

ALLOCATE EXTENT(SIZE integer [K | M] INSTANCE integer)

针对数据表操作的完整语法如下：

-----------

ALTER TABLE [schema.] table_name ALLOCATE EXTENT [({ SIZE integer [K | M] | DATAFILE 'filename' | INSTANCE integer})]

-----------

故，需要构建如下样子简单的SQL命令：

-----------

alter table TableName allocate extent

-----------

但要是每一张表写一条语句的话太过麻烦，为了方便我们使用SQL命令拼写出每一张表的alter语句。

构建对空表分配空间的SQL命令。

查询当前用户下的所有空表（一个用户最好对应一个默认表空间）。命令如下：

SQL>select table_name from user_tables where NUM_ROWS=0;

根据上述查询，可以构建针对空表分配空间的命令语句，如下：

SQL>Select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0 or num_rows is null（注意：很多教程没有这里，这里是有可能位空的）

上述代码可产生批量的修改表extent的SQL语句(有多少张空表就产生多少条)，我们只需要将其生成的所有sql代码全部执行，就可以给每一张已经存在的表来分配segment，就OK了。

最后：这时再用exp导出就没有问题了。但是：数据库本身的deferred_segment_creation属性还是TRUE,也是就是说如果再创建新表的话，默认还是不分配segment的。所以还是需要更改deferred_segment_creation的参数，以便以后创建的新表自动分配segment。

总结：

如果你的数据库还没有创建任何数据表，那么直接修改deferred_segment_creation属性，以后创建的表无论是不是为空都会自动分配segment，就不会出现导不出空表的情况。然而如果你的数据库中已经有很多空表，并且需要导出来，那么光修改deferred_segment_creation属性则没有用的，因为它只对之后创建的表有作用。你需要给已存在的空表分配segment以便可以导出存在的空表，就用到上面讲的allocate extent方法，但此方法只针对已经存在的表的segment属性，所以最好就是：先给已存在的空表分配segment，方便其可以直接导出，然后设定deferred_segment_creation参数以便以后每张表无论是否为空都自动分配segment。

附录：有关第三种方法给已经存在的空表分配segment，下面介绍一种生成脚本来执行sql的方法。

SQL>Select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0 or num_rows is null;

批量输出上述生成的SQL语句并写入到一个.sql的脚本文件中。

如：

1. 创建执行脚本文件：我创建一个E:\sql_script.sql文件。内容如下：

set heading off;

set echo off;

set feedback off;

set termout on;

spool E:\sql_allocate.sql;

Select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0or num_rows is null;

spool off;

这个脚本的作用就是创建一个E:\sql_allocate.sql脚本文件，将Select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0 or num_rows is null的执行结果（就是给每张表生成segment的SQL代码）批量输出，存储到一个E:\sql_allocate.sql的脚本文件中。

2. 执行E:\sql_script.sql文件来生成“分配表空间的SQL代码”的脚本文件sql_allocate.sql。

命令如下：

SQL>@ E:\sql_script.sql; (也可写一个批处理文件，命令如下：sqlplus 用户名/密码@数据库 @E:\sql_script.sql)

执行完毕后，得到E:\sql_allocate.sql脚本文件(里面是给所有空表分配segment的SQL代码)。

打开该文件会看到，已经得到对所有空表分配空间的SQL语句。

3. 执行E:\sql_allocate.sql文件来对表分配空间。

命令如下：SQL>@ E:\sql_allocate.sql

执行完毕，表已更改。之前存在的空表已分配segment空间！

大功告成，此时执行exp命令，即可把包括空表在内的所有表，正常导出。

11.2 Database New Feature Deferred Segment Creation [Video] (文档 ID 887962.1)

类型: 状态: 上次主更新: 上次更新:

BULLETIN PUBLISHED 2013-12-6 2016-8-6

In this Document

Purpose

Scope

Details

References

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.1 to 11.2.0.1 [Release 11.2]
Information in this document applies to any platform.

PURPOSE

This bulletin provides information about the database version 11.2 new feature called Deferred Segment Creation also referred to as Segment Creation On-Demand.

SCOPE

Database Administrators and Developers.

DETAILS

Video - 11.2 New Feature Deferred Segment Creation (10:04)

The Oracle Database 11.2 includes a new space allocation method. When you create a non-partitioned heap table, the table segment creation is deferred to the first row insert. This functionality is enabled by default with the initialization parameter DEFERRED_SEGMENT_CREATION set to TRUE.

The advantages of this new space allocation method are:

? A significant amount of disk space can be saved for applications that create hundreds or thousands of tables upon installation, many of which might never be populated.

? The application installation time is reduced, because the creation of a table is a data dictionary operation only.

When you insert the first row into the table, the segments are created for the base table, its LOB columns, and its indexes. During segment creation, cursors on the table are invalidated. These operations have a small additional impact on performance.

Note: With this new allocation method, it is essential that you do proper capacity planning so that the database has enough disk space to handle segment creation when tables are populated. For more details, see the Oracle Database Administrator's Guide.

SQL> connect / as sysdba
Connected.

SQL> show parameter deferred

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE

SQL> connect imran/imran
Connected.

SQL> create table  t1(no number);

Table created.

SQL> select * from user_segments;

no rows selected

SQL> insert into t1 values(10);

1 row created.

SQL> commit;

Commit complete.

SQL> select segment_name from user_segments;

SEGMENT_NAME
--------------------------------------------------------------------------------
T1

SQL> select table_name,segment_created from user_tables;

TABLE_NAME                     SEG
------------------------------ ---
T1                             YES

? This slide shows you how to verify the segment creation on demand. First you check the parameter DEFERRED_SEGMENT_CREATION . Then a table is created without segments, which you can verify by querying the USER_SEGMENTS data dictionary view. After the insert of a row, you query this view again, to see that the segment now exists.

? You can also query the SEGMENT_CREATED column of the USER_TABLES, USER_INDEXES, or USER_LOBS views. For non-partitioned tables, indexes and lobs, this column shows YES, if the segment is created. Note that when you create a table with deferred segment creation (the default), the new table appears in the *_TABLES views, but no entry for it appears in the *_SEGMENTS views until you insert the first row.

? Another addition to the data dictionary is the SYS.SEG$ table that stores the storage parameters which you specified during the table or index creation.

You can control segment creation in two ways:

1) With the DEFERRED_SEGMENT_CREATION initialization parameter set to TRUE or FALSE. This parameter can be set in the initialization file. You can also control it via the ALTER SESSION or ALTER SYSTEM commands.

Example:

SQL> connect / as sysdba
Connected.
SQL> show parameter def

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE
SQL> alter system set deferred_segment_creation=FALSE;

System altered.

SQL> show parameter def

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     FALSE

SQL> connect imran/imran
Connected.

SQL> create table t2(no number);

Table created.

SQL> select segment_name from user_segments;

SEGMENT_NAME
--------------------------------------------------------------------------------
T2

SQL> alter session set deferred_segment_creation=true;

Session altered.

SQL> create table t3( no number);

Table created.

SQL> select segment_name from user_segments;

SEGMENT_NAME
--------------------------------------------------------------------------------
T2

SQL>

2) With the SEGMENT CREATION clause of the CREATE TABLE command:

SEGMENT CREATION DEFERRED: If specified, segment creation is deferred until the first row is inserted into the table. This is the default behavior for the Oracle Database 11gR2.

SEGMENT CREATION IMMEDIATE: If specified, segments are materialized during table creation. This is the default behavior in Oracle databases prior to the Oracle Database 11gR2.

Example

SQL> select * from user_segments;

no rows selected

SQL> CREATE TABLE SEG_TAB3(C1 number, C2 number)
SEGMENT CREATION IMMEDIATE;

Table created.

SQL> select segment_name from user_segments;

SEGMENT_NAME
--------------------------------------------------------------------------------
SEG_TAB3

SQL>CREATE TABLE SEG_TAB4(C1 number, C2 number)
SEGMENT CREATION DEFERRED;

Table created.

SQL> select segment_name from user_segments;

SEGMENT_NAME
--------------------------------------------------------------------------------
SEG_TAB3

SQL>

This clause takes precedence over the DEFERRED_SEGMENT_CREATION parameter. It is possible to force creation of segments for an already created table with the ALTER TABLE …MOVE command. However, it is not possible to directly control the deferred segment creation for dependant objects like indexes. They inherit this characteristic from their parent object, in this case, the table.

Restrictions and Exceptions :-

?To enable deferred segment creation, compatibility must be set to '11.2.0' or higher.

? In the Oracle Database 11.2.0.1, deferred segment creation is restricted to non-partitioned tables and non-partitioned indexes. Deferred segment creation is not supported for partitioned indexes, bitmap join indexes, and domain indexes.

? IOTs and other special tables like clustered tables, global temporary tables, session-specific temporary tables, internal tables, typed tables, AQ tables, external tables are not supported. Tables owned by SYS, SYSTEM, PUBLIC, OUTLN, and XDB are also excluded.

? Segment creation on demand is not supported for tables created in dictionary-managed tablespaces and for clustered tables. If you try creating these tables, segments ARE created.

? Also please note: If you create a table with deferred segment creation on a locally managed tablespace, then it has no segments. If at a later time, you migrate the tablespace to dictionary-managed, then any attempt to create segments produces errors. In this case you must drop the table and recreate it.

Note:

In Release 11.2.0.1, deferred segment creation is not supported for partitioned tables. This restriction is removed in release 11.2.0.2 and later.

Notes on Tables Without Segments :-

The following rules apply to a table whose segment has not yet been materialized:

If you create this table with CREATE TABLE ... AS subquery, then if the source table has no rows, segment creation of the new table is deferred. If the source table has rows, then segment creation of the new table is not deferred.

If you specify ALTER TABLE ... ALLOCATE EXTENT before the segment is materialized, then the segment is materialized and then an extent is allocated. However the ALLOCATE EXTENT clause in a DDL statement on any indexes of the table will return an error.

During an EXCHANGE of a partition or subpartition with a non-partitioned table without a segment, segments are materialized for the non-partitioned table automatically before proceeding with the EXCHANGE.

When you issue an ALTER TABLE ... MOVE statement any storage properties you specify override the storage properties specified in the CREATE TABLE statement.

In a DDL statement on the table or its LOB columns or indexes, any specification of DEALLOCATE UNUSED is silently ignored.

ONLINE operations on indexes of a table without a segment will silently be disabled; that is, they will proceed OFFLINE.

Parallel DML operations on tables with no segments are disabled.

Additional Automatic Functionality:-

Additional enhancements in the Oracle Database 11gR2 (unrelated to the deferred segment creation) are implemented to save space: all UNUSABLE indexes and index partitions are created without a segment. This functionality is completely transparent for you. It is enabled by default with the COMPATIBILITY initialization parameter set to 11.2.0.0.

? Creating an index without a segment

CREATE INDEX test_i1 ON seg_test(c) UNUSABLE;

? Removing any allocated space for an index

ALTER INDEX test_i UNUSABLE;

? Creating the segment for an index:

ALTER INDEX test_i REBUILD;

The following slide shows some SQL commands which you might find useful for this new functionality:

? You can create an index without segment with the CREATE INDEX … UNUSABLE clause.
? You can remove any allocated space with the ALTER INDEX … UNUSABLE clause.
? And finally, you can create a segment for an index with the ALTER INDEX … REBUILD

Example

SQL> select * from user_segments ;

no rows selected

SQL> create table test_table(no number);

Table created.

SQL> insert into test_table values(10);

1 row created.

SQL> commit;

Commit complete.

SQL> select segment_name from user_segments;

SEGMENT_NAME
--------------------------------------------------------------------------------
TEST_TABLE

SQL> create index test_index on test_table(no) unusable;

Index created.

SQL> select segment_name from user_segments;

SEGMENT_NAME
--------------------------------------------------------------------------------
TEST_TABLE

SQL> alter index test_index rebuild;

Index altered.

SQL> select segment_name from user_segments;

SEGMENT_NAME
--------------------------------------------------------------------------------
TEST_TABLE
TEST_INDEX

SQL> alter index test_index unusable;

Index altered.

SQL> select segment_name from user_segments;

SEGMENT_NAME
--------------------------------------------------------------------------------
TEST_TABLE

SQL>

Reference

Oracle? Database SQL Language Reference
11g Release 2 (11.2)
Part Number E10592-02

About Me

.............................................................................................................................................

● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除

● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址：http://www.cnblogs.com/lhrbest

● 本文pdf版、个人简介及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● DBA宝典今日头条号地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

.............................................................................................................................................

● QQ群号：230161599（满）、618766405

● 微信群：可加我微信，我拉大家进群，非诚勿扰

● 联系我请加QQ好友（646634621），注明添加缘由

● 于 2017-08-01 06:00 ~ 2017-08-31 24:00 在魔都完成

● 最新修改时间： 2018-03-22 18:00

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

.............................................................................................................................................

● 小麦苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

● 小麦苗出版的数据库类丛书：http://blog.itpub.net/26736162/viewspace-2142121/

● 好消息：小麦苗OCP、OCM开班啦，详情请点击：http://blog.itpub.net/26736162/viewspace-2148098/

.............................................................................................................................................

使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号（xiaomaimiaolhr）及QQ群（DBA宝典），学习最实用的数据库技术。

小麦苗的微信公众号小麦苗的DBA宝典QQ群2 《DBA笔试面试宝典》读者群小麦苗的微店

.............................................................................................................................................

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26736162/viewspace-2143238/，如需转载，请注明出处，否则将追究法律责任。

ORACLE 11g 用exp命令导出库文件备份时，发现只能导出来一部分表而且不提示错误，之前找不到解决方案只能把没导出来的表重新建建立。后来发现是所有的空表都没有导出来。于是想好好查查,因为在以前的10g版本中没有这样的问题。

查资料发现Oracle 11g中有个新特性：新增了一个参数“deferred_segment_creation”含义是段延迟创建，默认是true。

具体是什么意思呢？

用下面的SQL语句查询，可以发现没有导出的表其 segment_created 字段值都是 'NO'。

Select segment_created,table_name from user_tables where segment_created = 'NO';

解决方法：

1、最原始最笨的办法(不推荐):insert一行，再rollback或者删除就产生segment了。

该方法是在在空表中插入数据，再删除，则产生segment。导出时则可导出空表。

2、设置deferred_segment_creation 参数：

设置deferred_segment_creation 参数为FALSE来禁用"段推迟创建"(也就是直接创建segment)，无论是空表还是非空表，都分配segment。

在sqlplus中，执行如下命令：

SQL>alter system set deferred_segment_creation=false;

查看：

SQL>show parameter deferred_segment_creation;

注意：该值设置后只对后面新增的表产生作用，对之前建立的空表(已经存在的)不起作用，仍不能导出。

并且要重新启动数据库，让参数生效。

3、使用ALLOCATE EXTENT，可以导出之前已经存在的空表。

使用ALLOCATE EXTENT可以为数据库对象的每一张表分配Extent(注意针对每一张表，就是说一张表需要一条SQL代码)：

其语法如下：

-----------

ALLOCATE EXTENT { SIZE integer [K | M] | DATAFILE 'filename' | INSTANCE integer }

-----------

可以针对数据表、索引、物化视图等手工分配Extent。

ALLOCATE EXTENT使用样例:

ALLOCATE EXTENT

ALLOCATE EXTENT(SIZE integer [K | M])

ALLOCATE EXTENT(DATAFILE 'filename')

ALLOCATE EXTENT(INSTANCE integer) www.2cto.com

ALLOCATE EXTENT(SIZE integer [K | M] DATAFILE 'filename')

ALLOCATE EXTENT(SIZE integer [K | M] INSTANCE integer)

针对数据表操作的完整语法如下：

-----------

ALTER TABLE [schema.] table_name ALLOCATE EXTENT [({ SIZE integer [K | M] | DATAFILE 'filename' | INSTANCE integer})]

-----------

故，需要构建如下样子简单的SQL命令：

-----------

alter table TableName allocate extent

-----------

但要是每一张表写一条语句的话太过麻烦，为了方便我们使用SQL命令拼写出每一张表的alter语句。

构建对空表分配空间的SQL命令。

查询当前用户下的所有空表（一个用户最好对应一个默认表空间）。命令如下：

SQL>select table_name from user_tables where NUM_ROWS=0;

根据上述查询，可以构建针对空表分配空间的命令语句，如下：

SQL>Select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0 or num_rows is null（注意：很多教程没有这里，这里是有可能位空的）

总结：

附录：有关第三种方法给已经存在的空表分配segment，下面介绍一种生成脚本来执行sql的方法。

SQL>Select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0 or num_rows is null;

批量输出上述生成的SQL语句并写入到一个.sql的脚本文件中。

如：

1. 创建执行脚本文件：我创建一个E:\sql_script.sql文件。内容如下：

set heading off;

set echo off;

set feedback off;

set termout on;

spool E:\sql_allocate.sql;

Select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0or num_rows is null;

spool off;

2. 执行E:\sql_script.sql文件来生成“分配表空间的SQL代码”的脚本文件sql_allocate.sql。

命令如下：

SQL>@ E:\sql_script.sql; (也可写一个批处理文件，命令如下：sqlplus 用户名/密码@数据库 @E:\sql_script.sql)

执行完毕后，得到E:\sql_allocate.sql脚本文件(里面是给所有空表分配segment的SQL代码)。

打开该文件会看到，已经得到对所有空表分配空间的SQL语句。

3. 执行E:\sql_allocate.sql文件来对表分配空间。

命令如下：SQL>@ E:\sql_allocate.sql

执行完毕，表已更改。之前存在的空表已分配segment空间！

大功告成，此时执行exp命令，即可把包括空表在内的所有表，正常导出。

11.2 Database New Feature Deferred Segment Creation [Video] (文档 ID 887962.1)

类型: 状态: 上次主更新: 上次更新:

BULLETIN PUBLISHED 2013-12-6 2016-8-6

In this Document

Purpose

Scope

Details

References

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.1 to 11.2.0.1 [Release 11.2]
Information in this document applies to any platform.

PURPOSE

This bulletin provides information about the database version 11.2 new feature called Deferred Segment Creation also referred to as Segment Creation On-Demand.

SCOPE

Database Administrators and Developers.

DETAILS

Video - 11.2 New Feature Deferred Segment Creation (10:04)

Note:

In Release 11.2.0.1, deferred segment creation is not supported for partitioned tables. This restriction is removed in release 11.2.0.2 and later.

Notes on Tables Without Segments :-

Reference

Oracle? Database SQL Language Reference
11g Release 2 (11.2)
Part Number E10592-02

码农公寓

DEFERRED_SEGMENT_CREATION

APPLIES TO:

PURPOSE

SCOPE

DETAILS

APPLIES TO:

PURPOSE

SCOPE

DETAILS

相关文章