EXP中参数compress=y是否导致imp时initial值的变化

EXP中compress=y是否导致imp时initial值的变化

 

举例证明：

导出库版本、平台：11.2，windows

导入库版本、平台：10.2，linux

 

1.创建测试表：

SQL> conn scott/xyc
已连接。
SQL> create table test_c as select * from sys.dba_objects;

表已创建。


 

2.查看导出库中表test_c的initial大小

SQL> set pagesize 0
SQL> set long 20000000000
SP2-0268: long 选项的编号无效
SQL> set long 2000000
SQL> select dbms_metadata.get_ddl('TABLE','TEST_C','SCOTT') from dual;

  CREATE TABLE "SCOTT"."TEST_C"
   (    "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)
   ) 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"

可以看到导出库中表test_c的initial为65536

3.查看表test-c占用空间

SQL> select segment_name, bytes from user_segments where segment_name = 'TEST_C';

SEGMENT_NAME                        BYTES
------------------------------ ----------
TEST_C                            9437184

可以看到导出库中表test_c占用空间为9437184

4.导出表test_c

[oracle@s1 ~]$ exp scott/xyc@orcl compress=y file=/home/oracle/exptab_test_c.dmp tables=test_c;

Export: Release 10.2.0.1.0 - Production on Fri Apr 5 05:44:54 2013

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

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


 


5.导入表test_c

[oracle@s1 ~]$ imp scott/xyc file=/home/oracle/exptab_test_c.dmp tables=test_c

Import: Release 10.2.0.1.0 - Production on Fri Apr 5 05:47:23 2013

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table                       "TEST_C"      73167 rows imported
Import terminated successfully without warnings.

6.在导入库查看test_c表的initial值大小

SQL> select dbms_metadata.get_ddl('TABLE','TEST_C','SCOTT') from dual;

  CREATE TABLE "SCOTT"."TEST_C"
   (    "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 9437184 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"

可以看到导出库中表test_c的initial为9437184，和导出库中表test_c占用空间相等，说明EXP中compress=y导致了imp时initial值的变化

 

posted @ 2013-04-04 23:58  坚固66  阅读(315)  评论(0)  编辑  收藏  举报