在执行exp导出的时候，如果不指定compress=N会遇到的问题

问题描述：

在执行exp导出的时候，如果不指定compress=N会遇到的问题，那么exp会尝试压缩表的extent：根据表中所有的extent的大小创建衣蛾很大的

初始extent，将表的全部extent压缩到一个extent中。如果利用这样导出的dmp文件再导入到目标库的话，目标库创建的对应的表的初始extent

将会非常大！！这样即使truncate这张表后，保留的使用的空间依然会很大。

Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。

C:\Users\1>oradim -startup -sid orcl
DIM-00015: 无法启动已在运行的 ORACLE - 请先将其关闭。

C:\Users\1>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on 星期一 5月 27 22:55:22 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> conn / as sysdba
已连接。
SQL> create table t_test_truncate as select * from v$dba_object;
create table t_test_truncate as select * from v$dba_object
                                              *
第 1 行出现错误:
ORA-00942: 表或视图不存在


SQL> create table t_test_truncate as select * from v$dba_objects;
create table t_test_truncate as select * from v$dba_objects
                                              *
第 1 行出现错误:
ORA-00942: 表或视图不存在


SQL> create table t_test_truncate as select * from dba_objects;

表已创建。

SQL> insert into t_test_truncate  select * from t_test_truncate;

已创建72465行。

SQL> insert into t_test_truncate  select * from t_test_truncate;

已创建144930行。

SQL> insert into t_test_truncate  select * from t_test_truncate;

已创建289860行。

SQL> insert into t_test_truncate  select * from t_test_truncate;

已创建579720行。

SQL> insert into t_test_truncate  select * from t_test_truncate;

已创建1159440行。

SQL> insert into t_test_truncate  select * from t_test_truncate;

已创建2318880行。

SQL> commit;

提交完成。
SQL> set timing on
SQL> select count(*) from t_test_truncate;

  COUNT(*)
----------
   4637760

已用时间:  00: 00: 00.12

SQL>  select BYTES from dba_segments where segment_name = 'T_TEST_TRUNCATE';

     BYTES
----------
545259520

已用时间:  00: 00: 00.01

SQL> set pagesize 10000
SQL> select dbms_metadata.get_ddl('TABLE','T_TEST_TRUNCATE') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','T_TEST_TRUNCATE')
--------------------------------------------------------------------------------

  CREATE TABLE "SYS"."T_TEST_TRUNCATE"
   (    "OWNER" VARCHAR2(30),
        "OBJECT_NAME" VARCHAR2(128),
        "SUBOBJECT_NAME" VARCHAR2(30),
        "OBJECT_ID" NUMBER,
        "DATA_OBJECT_ID" NUMBER,
        "OBJECT_TYPE" VARCHAR2(19),
        "CREATED" DATE,
        "LAST_DDL_TIME" DATE,
        "TIMESTAMP" VARCHAR2(19),
        "STATUS" VARCHAR2(7),
        "TEMPORARY" VARCHAR2(1),
        "GENERATED" VARCHAR2(1),
        "SECONDARY" VARCHAR2(1),
        "NAMESPACE" NUMBER,
        "EDITION_NAME" VARCHAR2(30)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"



已用时间:  00: 00: 00.22

=======================================================

在另一个命令窗口执行下面导出命令

C:\Users\1>exp 'sys/wilson as sysdba' file=d:\t_test_truncate.dmp tables=t_test_truncate

Export: Release 11.2.0.1.0 - Production on 星期一 5月 27 23:04:07 2013

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


连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
服务器使用 AL32UTF8 字符集 (可能的字符集转换)

即将导出指定的表通过常规路径...
. . 正在导出表                 T_TEST_TRUNCATE导出了     4637760 行
成功终止导出, 没有出现警告。

=========================================

再回来，执行truncate这张表

SQL> show user
USER 为 "SYS"
SQL> truncate table t_test_truncate;

表被截断。

已用时间:  00: 00: 21.39
SQL> drop table t_test_truncate purge;

表已删除。

已用时间:  00: 00: 00.06

========================================

再回到另一个命令窗口，执行表的导入

C:\Users\1>imp 'sys/wilson as sysdba' file=d:\t_test_truncate.dmp fromuser=sys touser=sys

Import: Release 11.2.0.1.0 - Production on 星期一 5月 27 23:07:28 2013

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


连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

经由常规路径由 EXPORT:V11.02.00 创建的导出文件
已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入
导入服务器使用 AL32UTF8 字符集 (可能的字符集转换)
. 正在将 SYS 的对象导入到 SYS
. . 正在导入表               "T_TEST_TRUNCATE"导入了     4637760 行
成功终止导入, 没有出现警告。

SQL> set long 10000
SQL> select dbms_metadata.get_ddl('TABLE','T_TEST_TRUNCATE') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','T_TEST_TRUNCATE')
--------------------------------------------------------------------------------

  CREATE TABLE "SYS"."T_TEST_TRUNCATE"
   (    "OWNER" VARCHAR2(30),
        "OBJECT_NAME" VARCHAR2(128),
        "SUBOBJECT_NAME" VARCHAR2(30),
        "OBJECT_ID" NUMBER,
        "DATA_OBJECT_ID" NUMBER,
        "OBJECT_TYPE" VARCHAR2(19),
        "CREATED" DATE,
        "LAST_DDL_TIME" DATE,
        "TIMESTAMP" VARCHAR2(19),
        "STATUS" VARCHAR2(7),
        "TEMPORARY" VARCHAR2(1),
        "GENERATED" VARCHAR2(1),
        "SECONDARY" VARCHAR2(1),
        "NAMESPACE" NUMBER,
        "EDITION_NAME" VARCHAR2(30)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 545259520 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"

已用时间:  00: 00: 00.22
SQL> desc dba_segments;
名称                                      是否为空? 类型
----------------------------------------- -------- ----------------------------
OWNER                                              VARCHAR2(30)
SEGMENT_NAME                                       VARCHAR2(81)
PARTITION_NAME                                     VARCHAR2(30)
SEGMENT_TYPE                                       VARCHAR2(18)
SEGMENT_SUBTYPE                                    VARCHAR2(10)
TABLESPACE_NAME                                    VARCHAR2(30)
HEADER_FILE                                        NUMBER
HEADER_BLOCK                                       NUMBER
BYTES                                              NUMBER
BLOCKS                                             NUMBER
EXTENTS                                            NUMBER
INITIAL_EXTENT                                     NUMBER
NEXT_EXTENT                                        NUMBER
MIN_EXTENTS                                        NUMBER
MAX_EXTENTS                                        NUMBER
MAX_SIZE                                           NUMBER
RETENTION                                          VARCHAR2(7)
MINRETENTION                                       NUMBER
PCT_INCREASE                                       NUMBER
FREELISTS                                          NUMBER
FREELIST_GROUPS                                    NUMBER
RELATIVE_FNO                                       NUMBER
BUFFER_POOL                                        VARCHAR2(7)
FLASH_CACHE                                        VARCHAR2(7)
CELL_FLASH_CACHE                                   VARCHAR2(7)

SQL> select BYTES from dba_segments where segment_name = 'T_TEST_TRUNCATE';

     BYTES
----------
545259520

已用时间:  00: 00: 00.07