Oracle exp compress参数引起的空间浪费

今天碰到一个比较有趣的问题，记录一下。

客户需要将表结构导出来做测试，并不将内容导出。
$exp ydjy/ydjy file=/tmp/ggs.dmp ROWS=n OWNER=ydjy STATISTICS=none
并用以下语句导入：
$imp zhoul/zhoul file=/tmp/ydjy.dmp fromuser=ydjy touser=zhoul

导入的时候出现错误：
"CREATE TABLE "SHENFENGZHENGXIUGAI" ("AAC001" NUMBER(16, 0) NOT NULL ENABLE, "EAC001" VARCH"
"AR2(12) NOT NULL ENABLE, "AAA029" VARCHAR2(3) NOT NULL ENABLE, "AAE135" VAR"
"CHAR2(20) NOT NULL ENABLE, "AAA130" VARCHAR2(6), "AAC003" VARCHAR2(50) NOT "
"NULL ENABLE, "AAC004" VARCHAR2(1) NOT NULL ENABLE, "AAC005" VARCHAR2(3) NOT"
" NULL ENABLE, "AAC006" NUMBER(8, 0) NOT NULL ENABLE, "AAC007" NUMBER(8, 0),"
" "AAC009" VARCHAR2(3) NOT NULL ENABLE, "AAC010" VARCHAR2(100), "AAC011" VAR"
"CHAR2(3), "AAC012" VARCHAR2(3) NOT NULL ENABLE, "AAC014" VARCHAR2(3), "AAC0"
"15" VARCHAR2(3), "AAC017" VARCHAR2(3), "AAC020" VARCHAR2(3), "AAE005" VARCH"
"AR2(20), "AAE006" VARCHAR2(100), "AAE007" VARCHAR2(6), "AAE013" VARCHAR2(20"
"00), "AAZ308" NUMBER(16, 0), "AAE159" VARCHAR2(50), "AAB401" VARCHAR2(20), "
""PRSENO" NUMBER(12, 0) NOT NULL ENABLE) PCTFREE 10 PCTUSED 40 INITRANS 1 M"
"AXTRANS 255 STORAGE([color=red]INITIAL 192937984[/color] NEXT 1048576 FREELISTS 1 FREELIST GRO"
"UPS 1 BUFFER_POOL DEFAULT) LOGGING NOCOMPRESS"
IMP-00017: following statement failed with ORACLE error 1658:
"CREATE TABLE "AC01MERGE" ("AAC001" NUMBER(16, 0) NOT NULL ENABLE, "EAC001" "
"VARCHAR2(12) NOT NULL ENABLE, "AAA029" VARCHAR2(3) NOT NULL ENABLE, "AAE135"
"" VARCHAR2(20) NOT NULL ENABLE, "AAA130" VARCHAR2(6), "AAC003" VARCHAR2(50)"
" NOT NULL ENABLE, "AAC004" VARCHAR2(1) NOT NULL ENABLE, "AAC005" VARCHAR2(3"
") NOT NULL ENABLE, "AAC006" NUMBER(8, 0) NOT NULL ENABLE, "AAC007" NUMBER(8"
", 0), "AAC009" VARCHAR2(3) NOT NULL ENABLE, "AAC010" VARCHAR2(100), "AAC011"
"" VARCHAR2(3), "AAC012" VARCHAR2(3) NOT NULL ENABLE, "AAC014" VARCHAR2(3), "
""AAC015" VARCHAR2(3), "AAC017" VARCHAR2(3), "AAC020" VARCHAR2(3), "AAE005" "
"VARCHAR2(20), "AAE006" VARCHAR2(100), "AAE007" VARCHAR2(6), "AAE013" VARCHA"
"R2(2000), "AAZ308" NUMBER(16, 0), "AAE159" VARCHAR2(50), "AAB401" VARCHAR2("
"20), "PRSENO" NUMBER(12, 0) NOT NULL ENABLE) PCTFREE 10 PCTUSED 40 INITRAN"
"S 1 MAXTRANS 255 STORAGE(INITIAL 3145728 NEXT 1048576 FREELISTS 1 FREELIST "
"GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS"
IMP-00003: ORACLE error 1658 encountered
ORA-01658: unable to create INITIAL extent for segment in tablespace USERS
IMP-00017: following statement failed with ORACLE error 1658:

查看了一下数据文件是自动扩展打开的
FILE_NAME AUT
------------------------------ ---
/lank/db/lank/users01.dbf YES

怀疑自动扩展是不是有bug，手工扩展一下数据文件，发现空间不足，当然出现此类错误，最好解决了，解决过程不说明了。
SQL> alter database datafile '/lank/db/lank/users01.dbf' resize 120m;
alter database datafile '/lank/db/lank/users01.dbf' resize 120m
*
ERROR at line 1:
ORA-01237: cannot extend datafile 4
ORA-01110: data file 4: '/lank/db/lank/users01.dbf'
ORA-19502: write error on file "/lank/db/lank/users01.dbf", blockno 14721
(blocksize=8192)
ORA-27072: File I/O error
Linux-x86_64 Error: 28: No space left on device
Additional information: 4
Additional information: 14721
Additional information: -1

导入表结构发现这张表竟然达到了190M，而这张表为空，到这里有经验的人可能一下子看出来了原因了


SEGMENT_NAME BYTES
------------------------------ ----------
SHENFENGZHENGXIUGAI 192937984

原来在exp时有个参数COMPRESS主要用于是否将segment的extent在导出时是否合并，而在上述导出语句中，我们可以看到compress没有加，也就是说保持默认，也就是说导出时Oracle将表结构的extent合并到一个extent中。
Keyword Description (Default) Keyword Description (Default)
--------------------------------------------------------------------------
USERID username/password FULL export entire file (N)
BUFFER size of data buffer OWNER list of owner usernames
FILE output files (EXPDAT.DMP) TABLES list of table names
COMPRESS import into one extent (Y) RECORDLENGTH length of IO record
GRANTS export grants (Y) INCTYPE incremental export type
INDEXES export indexes (Y) RECORD track incr. export (Y)
DIRECT direct path (N) TRIGGERS export triggers (Y)
LOG log file of screen output STATISTICS analyze objects (ESTIMATE)
ROWS export data rows (Y) PARFILE parameter filename
CONSISTENT cross-table consistency(N) CONSTRAINTS export constraints (Y)

OBJECT_CONSISTENT transaction set to read only during object export (N)
FEEDBACK display progress every x rows (0)
FILESIZE maximum size of each dump file
FLASHBACK_SCN SCN used to set session snapshot back to
FLASHBACK_TIME time used to get the SCN closest to the specified time
QUERY select clause used to export a subset of a table
RESUMABLE suspend when a space related error is encountered(N)
RESUMABLE_NAME text string used to identify resumable statement
RESUMABLE_TIMEOUT wait time for RESUMABLE
TTS_FULL_CHECK perform full or partial dependency check for TTS
VOLSIZE number of bytes to write to each tape volume
TABLESPACES list of tablespaces to export
TRANSPORT_TABLESPACE export transportable tablespace metadata (N)
TEMPLATE template name which invokes iAS mode export

Export terminated successfully without warnings.


问题重现：

SQL> conn ggs/ggs
Connected.
SQL> create table zhoul (name char(2000));

Table created.
可以看到刚创建出来的表格INITIAL_EXTENT为65536
SQL> select INITIAL_EXTENT,NEXT_EXTENT from user_tables where TABLE_NAME='ZHOUL';

INITIAL_EXTENT NEXT_EXTENT
-------------- -----------
65536 1048576

插入一定数据量之后，表格扩展到了75497472字节
SQL> select count(*) from zhoul;

COUNT(*)
----------
24576


SQL> select sum(BYTES) from user_extents where SEGMENT_NAME='ZHOUL';

SUM(BYTES)
----------
75497472

接下来用compress取不同值导出，观察建表脚本。
exp ggs/ggs tables=zhoul rows=n file=/tmp/zhoul02.dmp

[ora10g@linux-64 ~]$ strings /tmp/zhoul01.dmp
EXPORT:V10.02.01
DGGS
RTABLES
8192
Fri Oct 28 11:26:51 2011/tmp/zhoul01.dmp
#G#G
#G#G
+00:00
BYTE
UNUSED
INTERPRETED
DISABLE:ALL
METRICST
TABLE "ZHOUL"
CREATE TABLE "ZHOUL" ("NAME" CHAR(2000)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE([color=red]INITIAL 75497472[/color] NEXT 1048576 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS
METRICSTreferential integrity constraints
METRICET 0
METRICSTtriggers
METRICET 0
METRICSTbitmap, functional and extensible indexes
METRICET 0
METRICSTposttables actions
METRICET 0
METRICSTPost-inst procedural actions
METRICET 0
METRICETG0
EXIT
EXIT


exp ggs/ggs tables=zhoul rows=n compress=n file=/tmp/zhoul02.dmp

[ora10g@linux-64 ~]$ strings /tmp/zhoul02.dmp
EXPORT:V10.02.01
DGGS
RTABLES
8192
Fri Oct 28 11:27:46 2011/tmp/zhoul02.dmp
#G#G
#G#G
+00:00
BYTE
UNUSED
INTERPRETED
DISABLE:ALL
METRICST
TABLE "ZHOUL"
CREATE TABLE "ZHOUL" ("NAME" CHAR(2000)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE([color=red]INITIAL 65536[/color] NEXT 1048576 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS
METRICSTreferential integrity constraints
METRICET 0
METRICSTtriggers
METRICET 0
METRICSTbitmap, functional and extensible indexes
METRICET 0
METRICSTposttables actions
METRICET 0
METRICSTPost-inst procedural actions
METRICET 0
METRICETG0
EXIT
EXIT