使用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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26524307/viewspace-772585/