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 0SQL> set long 20000000000SP2-0268: long 选项的编号无效SQL> set long 2000000SQL> 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 2013Copyright (c) 1982, 2005, Oracle.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport done in US7ASCII character set and AL16UTF16 NCHAR character setserver uses AL32UTF8 character set (possible charset conversion)About to export specified tables via Conventional Path .... . exporting table                         TEST_C      73167 rows exportedExport terminated successfully without warnings.

 

5.导入表test_c

[oracle@s1 ~]$ imp scott/xyc file=/home/oracle/exptab_test_c.dmp tables=test_cImport: Release 10.2.0.1.0 - Production on Fri Apr 5 05:47:23 2013Copyright (c) 1982, 2005, Oracle.  All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsExport file created by EXPORT:V10.02.01 via conventional pathimport done in US7ASCII character set and AL16UTF16 NCHAR character setimport 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 importedImport 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值的变化