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(INITIAL 192937984 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
-----------