oracle exp 磁盘空间,Oracle exp compress参数引起的空间浪费

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

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

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(INITIAL 75497472 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(INITIAL 65536 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

分享到：