ITPub博客

首页 > 数据库 > Oracle > dmp很小,导入数据库后很大(compress参数)

dmp很小,导入数据库后很大(compress参数)

原创 Oracle 作者:ora_erin 时间:2013-09-11 20:48:26 0 删除 编辑
使用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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-04-16

  • 博文量
    37
  • 访问量
    631010