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"