Oracle清理高水位线操作，创建大表测试

数据库中经常会有delete数据，但是数据虽然删除，水位线并没有下降，所以查询不会因清理数据加快，表空间也并没有释放。

一般在建表前会考虑是否要经常清理表数据，若有需要则建立分区表；

但是很多情况下并没有长远考虑，或者字段没有时间分区，则可以采用expdp以及impdp的方式进行清理；

首先造一张3200万条数据的表：

-- 创建新表并批量插入100万条数据
create table TESTTABLE as 
select rownum as id,
               to_char(sysdate + ROWNUM/24/3600, 'yyyy-mm-dd hh24:mi:ss') as inc_datetime,
               trunc(dbms_random.value(0, 100)) as random_id,
               dbms_random.string('x', 20) random_string
          from dual
        connect by level <= 1000000;

这里再创建id字段为索引，查看清理高水位对索引的影响

 CREATE INDEX "WJZ"."PK_INDEX_ID" ON "WJZ"."TESTTABLE" ("ID") 

然后使用TESTTABLE表自己捣鼓几次到3200万条数据

insert into TESTTABLE
select t.id+16000000,t.inc_datetime,t.random_id,t.random_string from TESTTABLE t;

TESTTABLE 表数据量是3200万，表空间占用1920M，索引888M 

查询表大小： 

select owner,tablespace_name,SEGMENT_NAME ,segment_type,sum(BYTES/1024/1024) "sizes(MB)" 
from dba_segments where  owner='WJZ' 
group by owner,segment_name,segment_type, tablespace_name 
order by "sizes(MB)"  desc;

删除id>=10000的数据

delete from TESTTABLE t
where id>=12000000;

我的机器内存小，删除数据花了十几分钟，删除数据后查询表空间并没有变化；

再重新插入1200万条数据查看：

insert into TESTTABLE
select t.id+32000000,t.inc_datetime,t.random_id,t.random_string from TESTTABLE t;

表和索引占用的表空间没有增加，说明dml操作产生的空闲空间总是优先于扩展的空间使用；当然也有例外，比如以append方式insert数据。

 通过以下操作后，高水位线会释放；再查询表大小就降下来了。

导出、导入：

expdp wjz/password tables=TESTTABLE_BAK0621  directory=DATA_WJZ dumpfile=wjz0320_2.dmp job_name=wjz0320_2 logfile=wjz0320_2.log

impdp wjz/password tables=TESTTABLE_BAK0621  directory=DATA_WJZ dumpfile=wjz0320_2.dmp table_exists_action=replace job_name=wjz0320_2 logfile=impwjz0320_2.log

在expdp导出的时候可以看到数据预估有2.12G，这是占用表空间的大小，而实际导出后是1.15G大小。

参考文章：

Oracle实例之HWM（高水位线）性能优化 - 腾讯云开发者社区-腾讯云