exp匯出compress引數導致的imp時擴充套件太大

文章版權所有Jusin Hao（luckyfriends），支援原創，轉載請註明。

1.1.1. 問題現象：

資料庫imp匯入時報如下錯誤，dmp檔案只有120M，而匯入後資料庫佔用表空間多大幾十個G；導致磁碟空間迅速增長；

. 正在匯入表 "DAP_BASDOCIMP"匯入了 0 行

. . 正在匯入表 "DAP_BASDOCLIST"匯入了 0 行

. . 正在匯入表 "DAP_BATCH_Q"匯入了 0 行

. . 正在匯入表 "DAP_BILLFACTOR"匯入了 367 行

. . 正在匯入表 "DAP_BILLLOGS"匯入了 0 行

IMP-00017: 由於 ORACLE 錯誤 1659, 以下語句失敗:

"CREATE TABLE "DAP_CONTROLRULE" ("CONTROL" CHAR(15) NOT NULL ENABLE, "DR" NU"

"MBER(10, 0), "PK_BILLTYPE" VARCHAR2(20) NOT NULL ENABLE, "PK_CONTROLRULE" C"

"HAR(20) NOT NULL ENABLE, "PK_CORP" CHAR(4) NOT NULL ENABLE, "PK_GLBOOK" CHA"

"R(20), "PK_GLORG" CHAR(20), "TS" CHAR(19)) PCTFREE 10 PCTUSED 40 INITRANS "

"1 MAXTRANS 255 STORAGE(INITIAL 20480000 FREELISTS 1 FREELIST GROUPS 1 BUFFE"

"R_POOL DEFAULT) TABLESPACE "NNC_DATA01" LOGGING NOCOMPRESS"

IMP-00003: 遇到 ORACLE 錯誤 1659

ORA-01659: 無法分配超出 67 的 MINEXTENTS (在表空間 NNC_DATA01 中)

IMP-00017: 由於 ORACLE 錯誤 1659, 以下語句失敗:

1.1.2. 分析：

檢視錶空間大小

檢視段的盤區資訊等，發現初始盤區基本都比較大（基本都大於10M）

select segment_name,BYTES,BLOCKS,EXTENTS,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS from user_segments

檢視段對應的分配盤區大小都很大（客戶的資料實際沒有多少，很多表沒有資料）

Select a..segment_name,sum(bytes)/1024/1024 || ‘MB’from user_extents a group by a.segment_name;

檢視錶對應的塊數，同樣發現很多表實際並沒有資料；

select a.table_name, blocks,empty_blocks from user_tables a

測試：

測試1：

使用者Mslc1

create table a (a varchar2(20),b varchar2(20));

create table b (a varchar2(20),b varchar2(20));

insert into a values('ddddd1','ddddd2'); commit;

insert into a select * from a;反覆執行插入，commit；

C:\>exp system/oracle@orcl file=e:\mslc1.dmp compress=yes owner=mslc1 壓縮引數等於Y

C:\> exp system/oracle@orcl file=e:\mslc2.dmp compress=no owner=mslc1 壓縮引數等於N

可以發現加了compress=yes的備份，匯入時初始盤區是按照原有表的分配盤區來分配大小的；

C:\>imp system/oracle@orcl file=e:\mslc1.dmp fromuser=mslc1 touser=mslc2

加了compress=no的備份，匯入時初始盤區是按照正常預設值的來分配大小的；

C:\>imp system/oracle@orcl file=e:\mslc2.dmp fromuser=mslc1 touser=mslc3

測試2：

使用者Mslc1

create table a (a varchar2(20),b varchar2(20));

create table b (a varchar2(20),b varchar2(20));

insert into a values('ddddd1','ddddd2'); commit;

insert into a select * from a; 反覆執行插入，commit；

delete from a; commit；

C:\>exp system/oracle@orcl file=e:\mslc1.dmp compress=yes owner=mslc1

C:\> exp system/oracle@orcl file=e:\mslc2.dmp compress=no owner=mslc1

可以發現即使資料被刪除，加了compress=yes的備份，匯入時初始盤區還是按照原有表的分配盤區來分配大小的；

C:\>imp system/oracle@orcl file=e:\mslc1.dmp fromuser=mslc1 touser=mslc2

可以發現資料被刪除，加了compress=no的備份，匯入時初始盤區是按照正常預設值的來分配大小的；

C:\>imp system/oracle@orcl file=e:\mslc2.dmp fromuser=mslc1 touser=mslc3

1.1.3. 解決方法：

基於上面的分析，加了compress=yes引數會導致匯入時分配盤區按照原來表的分配空間大小來分配盤區（即使資料刪除了也是），因此匯出時不加該引數。

1.1.4. 參考：碎片整理

EXPORT/IMPORT一個很重要的應用方面就是整理碎片。因為如果時初次IMPPORT，就會重新CREATE TABLE 再匯入資料，所以整張表都是連續存放的。另外預設情況下EXPORT會在生成DUMP檔案是“壓縮(COMPRESS)”TABLE,但是這種壓縮在很多情況下被誤解。事實上，COMPRESS是改變STORAGE引數INITIAL的值。比如：
CREATE TABLE .... STORAGE( INITIAL 10K NEXT 10K..)現在資料已經擴充套件到100個EXTENT,如果採用COMPRESS=Y來EXPORT資料，則產生的語句時 STORAGE( INITIAL 1000K NEXT 10K)
我們可以看到NEXT值並沒有改變，而INITIAL是所有EXTENT的總和。所以會出現如下情況，表A有4個100M的EXTENT,執行DELETE FROM A，然後再用COMPRESS=Y 導 出資料，產生的CREATE TABLE語句將有400M的INITIAL EXTENT。即使這是TABLE中 已經沒有資料！！這是的DUMP檔案即使很小，但是在IMPORT時就會產生一個巨大的TABLE.
另外，也可能會超過DATAFILE的大小。比如，有4個50M的資料檔案，其中表A有15個10M的EXTENT,如果採用COMPRESS=Y的方式匯出資料，將會有INITIAL=150M，那麼在重新匯入時，不能分配一個150M的EXTENT，因為單個EXTENT不能跨多個檔案。

8、在USER和TABLESPACE之間傳送資料
一般情況下EXPORT的資料要恢復到它原來的地方去。如果SCOTT使用者的表以TABLE或USER方式EXPORT資料，在IMPORT時，如果SCOTT使用者不存在，則會報錯!以FULL方式匯出的資料帶有CREATE USER的資訊，所以會自己建立USER來存放資料。
當然可以在IMPORT時使用FROMUSER和TOUSER引數來確定要匯入的USER，但是要保證TOUSER一定已經存在啦。

http://www.cnblogs.com/advocate/archive/2010/12/14/1905974.html

http://www.bhcode.net/article/20101115/13975.html

http://dingchaoqun12.blog.163.com/blog/static/11606250420110184248721/

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/14710393/viewspace-1082733/，如需轉載，請註明出處，否則將追究法律責任。