建立测试表
我建立一个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
