Exp Empty Table in Oracle11gR2

on

先来看一下例子。我们创建一张表T2。

[sourcecode language=”sql” light=”true”]SQL> create table t2 (n number);

Table created.

SQL> desc t2
Name Null? Type
—————————————– ——– —————————-
N NUMBER[/sourcecode]

尝试使用exp将此表导出。

D:\Temp>exp kamus/oracle tables=t2

Export: Release 11.2.0.1.0 - Production on Fri Apr 16 18:11:51 2010

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Oracle Label Security, Data Mining and Real Application Testing opt
ions
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
EXP-00011: KAMUS.T2 does not exist
Export terminated successfully with warnings.

报错说这张表并不存在。这是让很多客户费解的地方,在测试库中创建应用的表结构,然后再将表结构exp到产品库中去,这是很多客户常用的方法,但是在11gR2中如果这些表是新创建的没有插入过任何一条记录,那么将会碰到上面这样的错误。

原因在于11gR2中的新功能 – Deferred Segment Creation(延迟段创建),默认情况下这个功能是启用的。

[sourcecode language=”sql” light=”true”]SQL> show parameter DEFERRED_SEGMENT_CREATION

NAME TYPE VALUE
———————————— ——————– ——————–
deferred_segment_creation boolean TRUE[/sourcecode]

延迟段创建的含义是当此新创建一个可能会有Segment的对象时(比如表、索引、物化视图等),如果这个对象中还没有任何记录需要消耗一个Extent,那么将不会在创建对象时自动创建Segment,这样做的好处无疑是在创建对象时大大提高了速度。

对于上例中的T2表,我们在创建结束就立刻检查DBA_SEGMENTS视图,会发现没有任何记录。

[sourcecode language=”sql” light=”true”]SQL> select segment_name from user_segments where segment_name=’T2′;

no rows selected[/sourcecode]

而对于exp程序而言,当仅仅存在Object的定义而没有相应的Segment时,就会报出EXP-00011对象不存在的错误。

解决方法就很简单了,以下方法任选其一。

1. 设置DEFERRED_SEGMENT_CREATION为FALSE,这样创建对象时就会自动创建Segment

2. 在创建对象时,明确指定立刻创建Segment
[sourcecode language=”sql” light=”true”]create table t2 (n number) SEGMENT CREATION IMMEDIATE;[/sourcecode]

3. 使用expdp替代exp(Datapump本身就是Oracle10g以后的推荐工具)

D:\Temp>expdp kamus/oracle tables=t2

Export: Release 11.2.0.1.0 - Production on Fri Apr 16 18:14:41 2010

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Oracle Label Security, Data Mining and Real Application Testing opt
ions
Starting "KAMUS"."SYS_EXPORT_TABLE_01":  kamus/******** tables=t2
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "KAMUS"."T2"                                    0 KB       0 rows
Master table "KAMUS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for KAMUS.SYS_EXPORT_TABLE_01 is:
  D:\ORACLE\ADMIN\ORCL\DPDUMP\EXPDAT.DMP
Job "KAMUS"."SYS_EXPORT_TABLE_01" successfully completed at 18:15:10

Update@2012-05-29
在最新的11.2.0.3中,该问题已经不再存在,即使没有segment,用exp也仍然可以正常导出。

SQL> host exp kamus/oracle tables=t_test

Export: Release 11.2.0.3.0 - Production on Tue May 29 14:56:21 2012

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                         T_TEST          0 rows exported
Export terminated successfully without warnings.

SQL> select segment_name from user_segments where segment_name='T_TEST';

no rows selected

7 Comments Add yours

  1. l1t says:

    我看标题就猜到了,但是该延迟功能默认是开启的?

  2. l1t says:

    导入的时候还能延迟创建段?

  3. Kamus says:

    11gR2中默认开启。导入(imp)的时候无法使用延迟创建特性,因此会报错。

  4. Bruce says:

    导入的时候还能延迟创建段?

  5. saihu says:

    Exp Utility is Deprecated in 11g

  6. Kamus says:

    D:\Temp>exp help=y

    Export: Release 11.2.0.1.0 – Production on Thu Jul 22 18:07:59 2010

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

    …..

    Still here.

Leave a Reply

Your email address will not be published. Required fields are marked *