查资料发现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)

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.

? 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:

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

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

 

Reference

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