[20120410]EXP&IMP和COMPRESS参数.txt

[20120410]EXP&IMP和COMPRESS参数.txt

测试需要要建立一个空库，我使用EXP&IMP操作，很久不使用它操作，发现导入过程有一些慢，最终发现一个参数compress影响导入操作。
缺省COMPRESS=Y,这样在导入时需要初始很大的空间。

测试如下：

SQL> select * from v$version ;

BANNER

--------------------------------------------------------------------------------

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

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

SQL> create table t as select rownum id,'test' name from dual connect by level

Table created.

1.查看空间使用：

SQL> select sum(blocks) as blocks, sum(bytes) as bytes  from user_extents where segment_name ='T';

    BLOCKS      BYTES

---------- ----------

       112     917504

SQL> SELECT dbms_metadata.get_ddl('TABLE','T') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','T')

---------------------------------------------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."T"

   (    "ID" NUMBER,

        "NAME" CHAR(4)

   ) SEGMENT CREATION IMMEDIATE

  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 DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS"

2.使用EMP文件：

$ exp userid=scott/xxx file=t.dmp log=t_log.txt tables=T COMPRESS=Y

Export: Release 11.2.0.1.0 - Production on Tue Apr 10 09:44:28 2012

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, OLAP, Data Mining and Real Application Testing options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...

. . exporting table                              T      50000 rows exported

Export terminated successfully without warnings.

--另外exp help=y ,可以发现缺省COMPRESS＝Y，意思是COMPRESS   import into one extent (Y)。

3.导入：

SQL> alter table T rename to TOLD;

$ imp userid=scott/xxx file=t.dmp log=imp_log.txt fromuser=scott touser=scott

Import: Release 11.2.0.1.0 - Production on Tue Apr 10 09:55:30 2012

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, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

. importing SCOTT's objects into SCOTT

. . importing table                            "T"      50000 rows imported

Import terminated successfully without warnings.

·

4.查询：

SQL> select sum(blocks) as blocks, sum(bytes) as bytes  from user_extents where segment_name ='T';

    BLOCKS      BYTES

---------- ----------

       112     917504

SQL> SELECT dbms_metadata.get_ddl('TABLE','T') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','T')

----------------------------------------------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."T"

   (    "ID" NUMBER,

        "NAME" CHAR(4)

   ) SEGMENT CREATION IMMEDIATE

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

  STORAGE(INITIAL 917504 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS"

--可以发现INITIAL=917504.这样如果导入的是空表，开始占用的空间就很大，当然我的测试是11G，段延迟的特性建立空表不占用空间。
--但是一旦分配空间，占用就很大。

5.改用COMPRESS=N参数

SQL> drop table t purge ;

Table dropped.

SQL> alter table told rename to t ;

Table altered.

$ exp userid=scott/xxx file=t.dmp log=t_log.txt tableOs=T COMPRESS=N

SQL> alter  table t rename to told ;

Table altered.

$ imp userid=scott/xxx file=t.dmp log=imp_log.txt fromuser=scott touser=scott

SQL> SELECT dbms_metadata.get_ddl('TABLE','T') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','T')

----------------------------------------------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."T"

   (    "ID" NUMBER,

        "NAME" CHAR(4)

   ) SEGMENT CREATION IMMEDIATE

  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 DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS"