oracle 11g导出空表 DEFERRED_SEGMENT_CREATION

今天遇到一问题：oracle11g导出imp,空表导不出。

 

DEFERRED_SEGMENT_CREATION

PropertyDescriptionParameter typeBooleanDefault valuetrueModifiableALTER SESSION, ALTER SYSTEMRange of valuestrue | falseBasicNo

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.

---如果DEFERRED_SEGMENT_CREATION值为true，则创建表或者依赖表的对象时不为表分配segment,直到第一行数据插入表。默认值为TRUE

 

--例1：查看参数deferred_segment_creation的值，scott创建表zml,导出表zml报错SQL> show parameter deferred_segment_creation;NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------deferred_segment_creation            boolean     TRUESQL> connect scott/zml已连接。SQL> create table zml(id int);表已创建。--导出表报错，表zml不存在C:\Users\ZML>exp scott/zml file='E:\zml.dmp' tables = (zml)Export: Release 11.2.0.1.0 - Production on 星期二 4月 22 21:28:30 2014Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集即将导出指定的表通过常规路径...EXP-00011: SCOTT.ZML 不存在导出成功终止, 但出现警告。

---例2：用system用户创建表zml1,导出表成功！----(同事说空表导不出来，我说我就可以导出来呀，，，原来我用system导的，哈哈哈哈)----------------SQL> connect system/zml已连接。SQL> show parameter deferred_segment_creation;NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------deferred_segment_creation            boolean     TRUESQL> create table zml1(id int);表已创建。---deferred_segment_creation为ture时system用户也可导出空表C:\Users\ZML>exp system/zml file='E:\zml1.dmp' tables = (zml1)Export: Release 11.2.0.1.0 - Production on 星期二 4月 22 21:31:33 2014Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集即将导出指定的表通过常规路径.... . 正在导出表                            ZML1导出了           0 行成功终止导出, 没有出现警告。

--例3：deferred_segment_creation设置为false,在scott用户下创建表zml2,可以导出空表。SQL> connect system/zml已连接。SQL> show parameter deferred_segment_creation;NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------deferred_segment_creation            boolean     TRUESQL> create table zml1(id int);表已创建。SQL> alter system set deferred_segment_creation = false;系统已更改。SQL> show parameter deferred_segment_creation;NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------deferred_segment_creation            boolean     FALSESQL> connect scott/zml已连接。SQL> create table zml2(id int);表已创建。----导出表zml2C:\Users\ZML>exp scott/zml file='E:\zml2.dmp' tables = (zml2)Export: Release 11.2.0.1.0 - Production on 星期二 4月 22 21:35:08 2014Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集即将导出指定的表通过常规路径.... . 正在导出表                            ZML2导出了           0 行成功终止导出, 没有出现警告。C:\Users\ZML>

解决方法:

1、先insert一条数据，再把数据delete掉。就可以导出了！

2、将参数deferred_segment_creation参数设置为false

3、 使用数据泵导出导入

4、allocate extent

以下为同事总结的，摘过来用用：

--allocate extentSELECT 'ALTER TABLE ' || T1.TABLE_NAME || ' ALLOCATE EXTENT;'  FROM USER_TABLES T1 WHERE T1.TABLE_NAME NOT IN       (SELECT SEGMENT_NAME FROM USER_SEGMENTS WHERE SEGMENT_TYPE = 'TABLE')---但是，如果表中有分区表的话，则会报错：ORA-14254: 不能为按 (组合)“范围”或“列表”分区的表指定 ALLOCATE STORAGE；所以要使用一下语句来执行:---SELECT DECODE(T2.TABLE_NAME,              NULL,              'ALTER TABLE ' || T1.TABLE_NAME || ' ALLOCATE EXTENT;',              'ALTER TABLE ' || T1.TABLE_NAME || ' MODIFY PARTITION ' || T2.PARTITION_NAME || ' ALLOCATE EXTENT;')  FROM USER_TABLES T1  LEFT JOIN USER_TAB_PARTITIONS T2    ON T1.TABLE_NAME = T2.TABLE_NAME WHERE T1.TABLE_NAME NOT IN       (SELECT SEGMENT_NAME FROM USER_SEGMENTS WHERE SEGMENT_TYPE = 'TABLE');