ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20120410]EXP&IMP和COMPRESS参数.txt

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

原创 Linux操作系统 作者:lfree 时间:2012-04-10 10:12:35 0 删除 编辑
[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<=50000;
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"

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-720770/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2285
  • 访问量
    6025276