dmp很小，匯入資料庫後很大(compress引數)
使用exp/imp匯出的時候，發現有些dmp很小，但是匯入後資料庫之後佔用很大的空間，後來發現跟exp的compress參數有關，實驗如下：
SQL> select count(*) from hr.t_emp;
COUNT(*)
----------
4000000
SQL> select t.segment_name,t.bytes,t.blocks,t.extents from dba_segments t where segment_name='T_EMP';
SEGMENT_NAME BYTES BLOCKS EXTENTS
-------------------------------------------------------------------------------- ---------- ---------- ----------
T_EMP 75497472 9216 80
SQL> select extent_id,block_id,bytes,blocks from dba_extents where segment_name='T_EMP';
EXTENT_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ----------
0 433 65536 8
1 441 65536 8
2 449 65536 8
3 457 65536 8
4 465 65536 8
5 473 65536 8
6 481 65536 8
7 489 65536 8
8 497 65536 8
9 505 65536 8
10 513 65536 8
11 521 65536 8
12 529 65536 8
13 537 65536 8
14 545 65536 8
15 553 65536 8
16 649 1048576 128
17 777 1048576 128
18 905 1048576 128
19 1033 1048576 128
EXTENT_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ----------
20 1161 1048576 128
21 1289 1048576 128
22 1417 1048576 128
23 1545 1048576 128
24 1673 1048576 128
25 1801 1048576 128
26 1929 1048576 128
27 2057 1048576 128
28 2185 1048576 128
29 2313 1048576 128
30 2441 1048576 128
31 2569 1048576 128
32 2697 1048576 128
33 2825 1048576 128
34 2953 1048576 128
35 3081 1048576 128
36 3209 1048576 128
37 3337 1048576 128
38 3465 1048576 128
39 3593 1048576 128
40 3721 1048576 128
EXTENT_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ----------
41 3849 1048576 128
42 3977 1048576 128
43 4105 1048576 128
44 4233 1048576 128
45 4361 1048576 128
46 4489 1048576 128
47 4617 1048576 128
48 4745 1048576 128
49 4873 1048576 128
50 5001 1048576 128
51 5129 1048576 128
52 5257 1048576 128
53 5385 1048576 128
54 5513 1048576 128
55 5641 1048576 128
56 5769 1048576 128
57 5897 1048576 128
58 6025 1048576 128
59 6153 1048576 128
60 6281 1048576 128
61 6409 1048576 128
EXTENT_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ----------
62 6537 1048576 128
63 6665 1048576 128
64 6793 1048576 128
65 6921 1048576 128
66 7049 1048576 128
67 7177 1048576 128
68 7305 1048576 128
69 7433 1048576 128
70 7561 1048576 128
71 7689 1048576 128
72 7817 1048576 128
73 7945 1048576 128
74 8073 1048576 128
75 8201 1048576 128
76 8329 1048576 128
77 8457 1048576 128
78 8585 1048576 128
79 8713 8388608 1024
80 rows selected
SQL> select segment_name,sum(bytes),sum(blocks),count(*) from dba_extents where segment_name='T_EMP' group by segment_name;
SEGMENT_NAME SUM(BYTES) SUM(BLOCKS) COUNT(*)
-------------------------------------------------------------------------------- ---------- ----------- ----------
T_EMP 75497472 9216 80
SQL> delete from hr.t_emp;--掛起
select * from v$session_wait where sid=154;--log file switch (checkpoint incomplete)
檢視alert日誌
ARC0: Failed to archive thread 1 sequence 75 (19809)
Thu May 16 10:07:20 2013
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_arc1_660.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0
remaining bytes available.
Thu May 16 10:07:20 2013
************************************************************************
You have following choices to free up space from flash recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
知道原因了，閃回恢復區已滿，不能歸檔新的日誌，這樣日誌就不能切換覆蓋老的日誌，所以DML就掛起了
SQL> select group#,sequence#,status,archived from v$log;
GROUP# SEQUENCE# STATUS ARCHIVED
---------- ---------- ---------------- --------
1 77 CURRENT NO
2 75 INACTIVE NO
3 76 INACTIVE NO
SQL>
SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 98.15 0 45
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0
6 rows selected
SQL> select sum(percent_space_used)*2/100 from V$FLASH_RECOVERY_AREA_USAGE;
SUM(PERCENT_SPACE_USED)*2/100
-----------------------------
1.963
rman連上去rman target sys/oracle@75 nocatalog
delete archivelog all;
SQL> delete from hr.t_emp;--執行成功
4000000 rows deleted
SQL> commit;
Commit complete
select t.segment_name,t.bytes,t.blocks,t.extents from dba_segments t where segment_name='T_EMP';
select extent_id,block_id,bytes,blocks from dba_extents where segment_name='T_EMP';
select segment_name,sum(bytes),sum(blocks),count(*) from dba_extents where segment_name='T_EMP'
group by segment_name;
--這些資料都沒有變化
匯出t_emp表，使用不同的compress引數
C:\>exp hr/hr@75 file=d:\t_emp_compress_n.dmp log=d:\t_emp_compress_n.log tables=t_emp compress=n
Export: Release 10.2.0.1.0 - Production on 星期四 5月 16 10:28:17 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
連線到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已匯出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
即將匯出指定的表通過常規路徑...
. . 正在匯出表 T_EMP匯出了 0 行
成功終止匯出, 沒有出現警告。
C:\>exp hr/hr@75 file=d:\t_emp_compress_y.dmp log=d:\t_emp_compress_y.log tables=t_emp compress=y
Export: Release 10.2.0.1.0 - Production on 星期四 5月 16 10:28:42 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
連線到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已匯出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
即將匯出指定的表通過常規路徑...
. . 正在匯出表 T_EMP匯出了 0 行
成功終止匯出, 沒有出現警告。
C:\>exp hr/hr@75 file=d:\t_emp_compress_default.dmp log=d:\t_emp_compress_default.log tables=t_emp
Export: Release 10.2.0.1.0 - Production on 星期四 5月 16 10:29:32 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
連線到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已匯出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
即將匯出指定的表通過常規路徑...
. . 正在匯出表 T_EMP匯出了 0 行
成功終止匯出, 沒有出現警告。
作業系統上看，這幾個檔案的大小沒啥區別：
C:\>d:
D:\>dir
驅動器 D 中的卷是 DATA
卷的序列號是 3A94-5E0B
D:\ 的目錄
2013-05-16 10:29 4,096 t_emp_compress_default.dmp
2013-05-16 10:29 318 t_emp_compress_default.log
2013-05-16 10:28 4,096 t_emp_compress_n.dmp
2013-05-16 10:28 318 t_emp_compress_n.log
2013-05-16 10:28 4,096 t_emp_compress_y.dmp
2013-05-16 10:28 318 t_emp_compress_y.log
D:\>
下面匯入看看
D:\>imp emr3/emr3@123 file=d:\t_emp_compress_n.dmp fromuser=hr touser=emr3
Import: Release 10.2.0.1.0 - Production on 星期四 5月 16 10:37:36 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
連線到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
經由常規路徑由 EXPORT:V10.02.01 建立的匯出檔案
警告: 這些物件由 HR 匯出, 而不是當前使用者
已經完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的匯入
. 正在將 HR 的物件匯入到 EMR3
. . 正在匯入表 "T_EMP"匯入了 0 行
IMP-00017: 由於 ORACLE 錯誤 942, 以下語句失敗:
"ALTER TABLE "T_EMP" ADD FOREIGN KEY ("PID") REFERENCES "T_DEPART" ("ID") ON"
" DELETE SET NULL ENABLE"
IMP-00003: 遇到 ORACLE 錯誤 942
ORA-00942: 表或檢視不存在
成功終止匯入, 但出現警告。
conn emr3/emr3@123
SQL> select t.segment_name,t.bytes,t.blocks,t.extents from dba_segments t where segment_name='T_EMP';
SEGMENT_NAME BYTES BLOCKS EXTENTS
-------------------------------------------------------------------------------- ---------- ---------- ----------
T_EMP 65536 8 1
D:\>imp emr4/emr4@124 file=d:\t_emp_compress_y.dmp fromuser=hr touser=emr4
Import: Release 10.2.0.1.0 - Production on 星期四 5月 16 10:41:08 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
連線到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
經由常規路徑由 EXPORT:V10.02.01 建立的匯出檔案
警告: 這些物件由 HR 匯出, 而不是當前使用者
已經完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的匯入
. 正在將 HR 的物件匯入到 EMR4
. . 正在匯入表 "T_EMP"匯入了 0 行
IMP-00017: 由於 ORACLE 錯誤 942, 以下語句失敗:
"ALTER TABLE "T_EMP" ADD FOREIGN KEY ("PID") REFERENCES "T_DEPART" ("ID") ON"
" DELETE SET NULL ENABLE"
IMP-00003: 遇到 ORACLE 錯誤 942
ORA-00942: 表或檢視不存在
成功終止匯入, 但出現警告。
conn emr4/emr4@124
SQL> select t.segment_name,t.bytes,t.blocks,t.extents from dba_segments t where segment_name='T_EMP';
SEGMENT_NAME BYTES BLOCKS EXTENTS
-------------------------------------------------------------------------------- ---------- ---------- ----------
T_EMP 75497472 9216 9
SQL> select extent_id,block_id,bytes,blocks from dba_extents where segment_name='T_EMP';
EXTENT_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ----------
0 553609 8388608 1024
1 554633 8388608 1024
2 555657 8388608 1024
3 556681 8388608 1024
4 557705 8388608 1024
5 558729 8388608 1024
6 559753 8388608 1024
7 560777 8388608 1024
8 561801 8388608 1024
9 rows selected
SQL> select segment_name,sum(bytes),sum(blocks),count(*) from dba_extents where segment_name='T_EMP' group by segment_name;
SEGMENT_NAME SUM(BYTES) SUM(BLOCKS) COUNT(*)
-------------------------------------------------------------------------------- ---------- ----------- ----------
T_EMP 75497472 9216 9
D:\>imp zjhis/zjhis@124 file=d:\t_emp_compress_default.dmp fromuser=hr touser=zjhis
Import: Release 10.2.0.1.0 - Production on 星期四 5月 16 10:44:33 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
連線到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
經由常規路徑由 EXPORT:V10.02.01 建立的匯出檔案
警告: 這些物件由 HR 匯出, 而不是當前使用者
已經完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的匯入
. 正在將 HR 的物件匯入到 ZJHIS
. . 正在匯入表 "T_EMP"匯入了 0 行
IMP-00017: 由於 ORACLE 錯誤 942, 以下語句失敗:
"ALTER TABLE "T_EMP" ADD FOREIGN KEY ("PID") REFERENCES "T_DEPART" ("ID") ON"
" DELETE SET NULL ENABLE"
IMP-00003: 遇到 ORACLE 錯誤 942
ORA-00942: 表或檢視不存在
成功終止匯入, 但出現警告。
conn zjhis/zjhis@124
select t.segment_name,t.bytes,t.blocks,t.extents from dba_segments t where segment_name='T_EMP' and owner='ZJHIS';
--這種情況同compress=y,因為compress=y是預設值嘛
現在答案已經很明顯了
我匯出的t_emp是空表，但是由於之前進行過大量的delete操作，空間是沒有釋放的
使用compress=n匯出，再匯入不會保留原先的空間分配，空間會釋放
使用compress=y匯出，再匯入，保留原先的段大小，但是extent的大小跟原表有所不同，由於oracle事先已經知道要分配這麼多的空間，所以一開始分配的extent就會比較大
開啟t_emp_compress_y.dmp
CREATE TABLE "T_EMP" ("CID" NUMBER, "CNAME" VARCHAR2(5), "PID" NUMBER(*,0)) PCTFREE 10 PCTUSED
40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 75497472 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS
開啟t_emp_compress_n.dmp
CREATE TABLE "T_EMP" ("CID" NUMBER, "CNAME" VARCHAR2(5), "PID" NUMBER(*,0)) PCTFREE 10 PCTUSED
40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS
可以看到compress=n的INITIAL小，compress=y的INITIAL大
這樣就解釋了為什麼dmp檔案很小，但是匯入之後庫很大的疑問
只要匯出的時候加compress=n就可以了
如果已經預設使用compress=y匯出，可以使用imp得到indexfile修改其initial，再進行匯入，這樣比較麻煩
參考資料：聯機文件Utilities
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), then the allocated space will be larger than the space
required to hold the data.
If you specify COMPRESS=n, then 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.
The COMPRESS parameter does not work with bitmapped tablespaces.
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, then 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.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26524307/viewspace-772585/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- OracleDatabase——資料庫表空間dmp匯出與匯入OracleDatabase資料庫
- oracle 9i的資料庫的dmp匯入到10G資料庫Oracle資料庫
- EXP匯出引數compress=y(n)的區別
- 然後再全庫匯入排除view資料庫在impdp匯入View資料庫
- 【匯入匯出】資料泵 job_name引數的使用
- oracle匯入dmp檔案Oracle
- 閃回資料庫之後匯入資料實驗資料庫
- truncate表後impdp匯入該表時加exclude=index引數並不能排除索引資料的匯入Index索引
- 資料庫 MySQL 資料匯入匯出資料庫MySql
- excel 整數匯入資料庫轉成varchar型別後多了小數點Excel資料庫型別
- plsql Oracle匯入dmp檔案SQLOracle
- 資料庫的匯入匯出資料庫
- mysql 資料庫匯入匯出MySql資料庫
- MySQL資料庫匯入匯出MySql資料庫
- 大文字資料，匯入匯出到資料庫資料庫
- 資料泵引數彙總與各種匯出匯入規則
- 【mysql】資料庫匯出和匯入MySql資料庫
- mysqldump匯入匯出mysql資料庫MySql資料庫
- oracle資料庫匯入匯出命令！Oracle資料庫
- Mysql 資料庫匯入與匯出MySql資料庫
- 匯入資料使用引數sort_area_size加快索引建立索引
- exp匯出compress引數導致的imp時擴充套件太大套件
- MySQL資料庫引數MySql資料庫
- Access 匯入 oracle 資料庫Oracle資料庫
- excel 匯入sqlyog資料庫ExcelSQL資料庫
- 將XML匯入資料庫XML資料庫
- 在SQL Server資料庫中匯入匯出資料SQLServer資料庫
- SQL資料庫的匯入和匯出SQL資料庫
- plsql developer匯入匯出資料庫方法SQLDeveloper資料庫
- oracle資料庫mmnl日誌很大Oracle資料庫
- 使用PARTITION_OPTIONS引數控制資料泵分割槽表匯入
- xml與資料庫中資料的匯入匯出XML資料庫
- SQL Server資料庫匯入匯出資料方式比較SQLServer資料庫
- 將informix匯出的文字資料匯入oracle資料庫ORMOracle資料庫
- TP5.1excel匯入資料庫的程式碼？php excel如何匯入資料庫？Excel資料庫PHP
- 匯入excel資源到資料庫Excel資料庫
- Oracle資料庫匯入匯出。imp匯入命令和exp匯出命令Oracle資料庫
- HHDBCS資料庫一鍵匯入資料庫