关于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:\Users\Administrator>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:\Users\Administrator>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:\Users\Administrator>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:\ORACLE\PRODUCT\10.2.0\ADMIN\BENDI\DPDUMP\BACK.DMPN
Job "SYSTEM"."TT" successfully completed at 21:58:10
实际只有31M左右。
我们然后倒入这个文件到另外一个用户看看HWM是否降低也就是
select BLOCKS*8/1024,segment_name from user_segments
是否降低为一半。

倒入记录
C:\Users\Administrator>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:\Users\Administrator>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:\Users\Administrator>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:\Users\Administrator>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://blog.itpub.net/7728585/viewspace-1062861/