oracle 11g导出空表 DEFERRED_SEGMENT_CREATION

32rewf

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

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.
---如果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     TRUE  
SQL> 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 2014  

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.  


连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production  
With 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     TRUE  
SQL> 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 2014  

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.  


连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production  
With 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     TRUE  
SQL> 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     FALSE  
SQL> connect scott/zml  
已连接。  
SQL> create table zml2(id int);  

表已创建。  
----导出表zml2  
C:\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 2014  

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.  


连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production  
With 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 extent  
SELECT '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');