1. 首页
  2. IT资讯

关于expdp ESTIMATE_only以及EXPDP和EXP HWM降低的比较

建立测试表
我建立一个CLOB字段,而且超过了4000字符in row模式,但是这种情况下就已经存储到了LOB SEGMENT中如下;
SQL> select BLOCKS*8/1024,segment_name from user_segments
  2  ;
 
BLOCKS*8/1024 SEGMENT_NAME
————- ——————————————————————————–
           72 SYS_LOB0000052797C00002$$
       0.3125 TESTN
         0.25 SYS_IL0000052797C00002$$
我的表一半是IT=1 一半是是IT=2 ,IM是CLOB字段
C:UsersAdministrator>expdp system/gelc123@bendi job_name=tt TABLES=ppzhu1.test
n     ESTIMATE_only=y;

Export: Release 10.2.0.4.0 – 64bit Production on Monday, 09 December, 2013 21:49
:27

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SYSTEM”.”TT”:  system/********@bendi job_name=tt TABLES=ppzhu1.testn E
STIMATE_only=y;
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated “PPZHU1″.”TESTN”                            72.31 MB
Total estimation using BLOCKS method: 72.31 MB
Job “SYSTEM”.”TT” successfully completed at 21:49:40

然后删除一半
SQL> delete testn where it=1;
 
2048 rows deleted
 
SQL> commit;
 
Commit complete

C:UsersAdministrator>expdp system/gelc123@bendi job_name=tt TABLES=ppzhu1.test
n     ESTIMATE_only=y;

Export: Release 10.2.0.4.0 – 64bit Production on Monday, 09 December, 2013 21:51
:24

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SYSTEM”.”TT”:  system/********@bendi job_name=tt TABLES=ppzhu1.testn E
STIMATE_only=y;
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated “PPZHU1″.”TESTN”                            72.31 MB
Total estimation using BLOCKS method: 72.31 MB
Job “SYSTEM”.”TT” successfully completed at 21:51:36

可以看到删除一半数据还是一样的,HWM没有降低这里ESTIMATE_only=y估计还是没有改变,可以判定这里估计得是HWM以下的所有,
而不是真正的。
导出过程证明了这个问题
C:UsersAdministrator>expdp system/gelc123@bendi job_name=tt TABLES=ppzhu1.test
n  dumpfile=DATA_PUMP_DIR:back.dmpn logfile=DATA_PUMP_DIR:expdp1.log

Export: Release 10.2.0.4.0 – 64bit Production on Monday, 09 December, 2013 21:57
:51

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SYSTEM”.”TT”:  system/********@bendi job_name=tt TABLES=ppzhu1.testn d
umpfile=DATA_PUMP_DIR:back.dmpn logfile=DATA_PUMP_DIR:expdp1.log
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 72.31 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “PPZHU1″.”TESTN”                            31.14 MB    2048 rows
Master table “SYSTEM”.”TT” successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.TT is:
  C:ORACLEPRODUCT10.2.0ADMINBENDIDPDUMPBACK.DMPN
Job “SYSTEM”.”TT” successfully completed at 21:58:10
实际只有31M左右。
我们然后倒入这个文件到另外一个用户看看HWM是否降低也就是
select BLOCKS*8/1024,segment_name from user_segments
是否降低为一半。

倒入记录
C:UsersAdministrator>impdp ppzhu2/gelc123@bendi  tables=PPZHU1.testn remap_sch
ema=ppzhu1:ppzhu2   dumpfile=DATA_PUMP_DIR:back.dmpn logfile=DATA_PUMP_DIR:expdp
1.log

Import: Release 10.2.0.4.0 – 64bit Production on Monday, 09 December, 2013 22:01
:40

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “PPZHU2”.”SYS_IMPORT_TABLE_01″ successfully loaded/unloaded
Starting “PPZHU2”.”SYS_IMPORT_TABLE_01″:  ppzhu2/********@bendi tables=PPZHU1.te
stn remap_schema=ppzhu1:ppzhu2 dumpfile=DATA_PUMP_DIR:back.dmpn logfile=DATA_PUM
P_DIR:expdp1.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “PPZHU2″.”TESTN”                            31.14 MB    2048 rows
Job “PPZHU2”.”SYS_IMPORT_TABLE_01″ successfully completed at 22:01:48

查看
SQL> select BLOCKS*8/1024,segment_name from user_segments
  2  ;
 
BLOCKS*8/1024 SEGMENT_NAME
————- ——————————————————————————–
       0.0625 SYS_IL0000052972C00002$$
           33 SYS_LOB0000052972C00002$$
       0.1875 TESTN
果然降低了HWM。
所以ESTIMATE_only=y只是测试HWM以下的,而不管是否为空,DELETE删除也会统计在内,导出会得到正常大小,倒入也会
降低HWM

最后测试一下EXP/IMP  CLOB降低了HWM表并没有降低
C:UsersAdministrator>imp ppzhu/gelc123@bendi fromuser=ppzhu1 touser=ppzhu2 tab
les=testn file=’c:test.dmpn’

Import: Release 10.2.0.4.0 – Production on Mon Dec 9 22:06:29 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by PPZHU1, not by you

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing PPZHU1’s objects into PPZHU2
. . importing table                        “TESTN”       2048 rows imported
Import terminated successfully without warnings.

SQL>  select BLOCKS*8/1024,segment_name from user_segments;
 
BLOCKS*8/1024 SEGMENT_NAME
————- ——————————————————————————–
       0.0625 SYS_IL0000052978C00002$$
           33 SYS_LOB0000052978C00002$$
       0.3125 TESTN   —-表并没有降低
      
但是如果加上 COMPRESS=n
C:UsersAdministrator>exp  ppzhu1/gelc123 COMPRESS=n tables=testn file=’c:test.dmpn1′;

SQL> select BLOCKS*8/1024,segment_name from user_segments;
 
BLOCKS*8/1024 SEGMENT_NAME
————- ——————————————————————————–
           33 SYS_LOB0000052981C00002$$
       0.1875 TESTN  —-降低HWM
       0.0625 SYS_IL0000052981C00002$$
就可以了。要注意一下。

最后注意一下,如果一个表DELETE所有的行,那么ESTIMATE_only=y看到就是0KB了虽然HWM还是没有降低,
这是特别之处。

C:UsersAdministrator>expdp system/gelc123@bendi job_name=tt TABLES=ppzhu2.test
n     ESTIMATE_only=y;

Export: Release 10.2.0.4.0 – 64bit Production on Monday, 09 December, 2013 22:16
:50

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SYSTEM”.”TT”:  system/********@bendi job_name=tt TABLES=ppzhu2.testn E
STIMATE_only=y;
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated “PPZHU2″.”TESTN”                                0 KB
Total estimation using BLOCKS method: 0 KB
Job “SYSTEM”.”TT” successfully completed at 22:17:02

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7728585/viewspace-1062861/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/183344.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code