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



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

 

举例证明：

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

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

 

1.创建测试表：

[sql] view plain copy
01.SQL> conn scott/xyc 
02.已连接。 
03.SQL> create table test_c as select * from sys.dba_objects; 
04. 
05.表已创建。 

 

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

[sql] view plain copy
01.SQL> set pagesize 0 
02.SQL> set long 20000000000 
03.SP2-0268: long 选项的编号无效 
04.SQL> set long 2000000 
05.SQL> select dbms_metadata.get_ddl('TABLE','TEST_C','SCOTT') from dual; 
06. 
07.  CREATE TABLE "SCOTT"."TEST_C" 
08.   (    "OWNER" VARCHAR2(30), 
09.        "OBJECT_NAME" VARCHAR2(128), 
10.        "SUBOBJECT_NAME" VARCHAR2(30), 
11.        "OBJECT_ID" NUMBER, 
12.        "DATA_OBJECT_ID" NUMBER, 
13.        "OBJECT_TYPE" VARCHAR2(19), 
14.        "CREATED" DATE, 
15.        "LAST_DDL_TIME" DATE, 
16.        "TIMESTAMP" VARCHAR2(19), 
17.        "STATUS" VARCHAR2(7), 
18.        "TEMPORARY" VARCHAR2(1), 
19.        "GENERATED" VARCHAR2(1), 
20.        "SECONDARY" VARCHAR2(1), 
21.        "NAMESPACE" NUMBER, 
22.        "EDITION_NAME" VARCHAR2(30) 
23.   ) SEGMENT CREATION IMMEDIATE 
24.  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING 
25.  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 
26.  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) 
27.  TABLESPACE "USERS" 

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

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

[sql] view plain copy
01.SQL> select segment_name, bytes from user_segments where segment_name = 'TEST_C'; 
02. 
03.SEGMENT_NAME                        BYTES 
04.------------------------------ ---------- 
05.TEST_C                            9437184 

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

4.导出表test_c

[plain] view plain copy
01.[oracle@s1 ~]$ exp scott/xyc@orcl compress=y file=/home/oracle/exptab_test_c.dmp tables=test_c; 
02. 
03.Export: Release 10.2.0.1.0 - Production on Fri Apr 5 05:44:54 2013 
04. 
05.Copyright (c) 1982, 2005, Oracle.  All rights reserved. 
06. 
07. 
08.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production 
09.With the Partitioning, OLAP, Data Mining and Real Application Testing options 
10.Export done in US7ASCII character set and AL16UTF16 NCHAR character set 
11.server uses AL32UTF8 character set (possible charset conversion) 
12. 
13.About to export specified tables via Conventional Path ... 
14.. . exporting table                         TEST_C      73167 rows exported 
15.Export terminated successfully without warnings. 

 

 5.导入表test_c

[sql] view plain copy
01.[oracle@s1 ~]$ imp scott/xyc file=/home/oracle/exptab_test_c.dmp tables=test_c 
02. 
03.Import: Release 10.2.0.1.0 - Production on Fri Apr 5 05:47:23 2013 
04. 
05.Copyright (c) 1982, 2005, Oracle.  All rights reserved. 
06. 
07. 
08.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production 
09.With the Partitioning, OLAP and Data Mining options 
10. 
11.Export file created by EXPORT:V10.02.01 via conventional path 
12.import done in US7ASCII character set and AL16UTF16 NCHAR character set 
13.import server uses WE8ISO8859P1 character set (possible charset conversion) 
14.. importing SCOTT's objects into SCOTT 
15.. importing SCOTT's objects into SCOTT 
16.. . importing table                       "TEST_C"      73167 rows imported 
17.Import terminated successfully without warnings. 

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

[sql] view plain copy
01.SQL> select dbms_metadata.get_ddl('TABLE','TEST_C','SCOTT') from dual; 
02. 
03.  CREATE TABLE "SCOTT"."TEST_C" 
04.   (    "OWNER" VARCHAR2(30), 
05.        "OBJECT_NAME" VARCHAR2(128), 
06.        "SUBOBJECT_NAME" VARCHAR2(30), 
07.        "OBJECT_ID" NUMBER, 
08.        "DATA_OBJECT_ID" NUMBER, 
09.        "OBJECT_TYPE" VARCHAR2(19), 
10.        "CREATED" DATE, 
11.        "LAST_DDL_TIME" DATE, 
12.        "TIMESTAMP" VARCHAR2(19), 
13.        "STATUS" VARCHAR2(7), 
14.        "TEMPORARY" VARCHAR2(1), 
15.        "GENERATED" VARCHAR2(1), 
16.        "SECONDARY" VARCHAR2(1), 
17.        "NAMESPACE" NUMBER, 
18.        "EDITION_NAME" VARCHAR2(30) 
19.   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING 
20.  STORAGE(INITIAL 9437184 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 
21.  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) 
22.  TABLESPACE "USERS" 

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