Oracle exp中的選項compress測試

Default: y

Specifies how Export and Import manage the initial extent for table data.

The default, COMPRESS=y, causes Export to flag table data for consolidation into one initial extent upon import. If extent sizes are large (for example, because of the PCTINCREASE parameter), the allocated space will be larger than the space required to hold the data.

If you specify COMPRESS=n, Export uses the current storage parameters, including the values of initial extent size and next extent size. The values of the parameters may be the values specified in the CREATE TABLE or ALTER TABLE statements or the values modified by the database system. For example, the NEXT extent size value may be modified if the table grows and if the PCTINCREASE parameter is nonzero.

Note:

Although the actual consolidation is performed upon import, you can specify the COMPRESS parameter only when you export, not when you import. The Export utility, not the Import utility, generates the data definitions, including the storage parameter definitions. Therefore, if you specify COMPRESS=y when you export, you can import the data in consolidated form only.

Note:

Neither LOB data nor subpartition data is compressed. Rather, values of initial extent size and next extent size at the time of export are used.

--===============================

COMPRESS=n很好理解，表匯出去時extent是什麼樣的匯入時還是原來的樣子；COMPRESS=y貌似oracle主要是應對PCTINCREASE導致的extent越來越大的問題；下面測試了一下LMT下autoallocate的情況倒是覺得可以理解。

根據測試COMPRESS=y時說明oracle匯入表時會根據segment的大小重新分配extent，COMPRESS=n時extent不做調整。

C:>sqlplus test/test

SQL*Plus: Release 11.2.0.1.0 Production on 星期日 9月 16 21:09:37 2012

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

串連到:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning option

SQL> create table tt tablespace users as select * from dba_objects;

表已建立。

SQL> insert into tt select * from tt;

已建立16963行。

SQL> commit;

提交完成。

本文URL地址：http://www.bianceng.cn/database/Oracle/201410/45563.htm

SQL> select extent_id,blocks from dba_extents where owner='TEST' and segment_name='TT';

EXTENT_ID BLOCKS

---------- ----------

0 8

1 8

2 8

3 8

4 8

5 8

6 8

7 8

8 8

9 8

10 8

EXTENT_ID BLOCKS

---------- ----------

11 8

12 8

13 8

14 8

15 8

16 128

17 128

18 128

已選擇19行。

--==============================

C:>exp test/test file=c:temptt_compress_y.dmp tables=tt compress=y

Export: Release 11.2.0.1.0 - Production on 星期日 9月 16 21:11:35 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

串連到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning option

已匯出 ZHS16GBK 字元集和 AL16UTF16 NCHAR 字元集

即將匯出指定的表通過常規路徑...

. . 正在匯出表 TT匯出了 33926 行

成功終止匯出, 沒有出現警告。

C:>exp test/test file=c:temptt_compress_n.dmp tables=tt compress=n

Export: Release 11.2.0.1.0 - Production on 星期日 9月 16 21:11:50 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.