数据交换程序异常引发的艰难的ORACLE数据提纯作业

昨日上班巡检数据库发现，其中一个数据库表空间一个晚上暴增了9G：

表空间状况：

因为晚上的WEB业务非常稀少，马上怀疑是数据交换出了问题，不是真的预订数据多就是数据交换程序出错。因为我们的交换程序有个特性，把所有的交换内容包括交换出错的详细信息都用CLOB字段保存在表里了。

于是用如下语句进一步查大字段对象：

1. --大字段 
2. SELECT A.TABLE_NAME, 
3.        A.COLUMN_NAME, 
4.        B.SEGMENT_NAME, 
5.        B.SEGMENT_TYPE, 
6.        B.TABLESPACE_NAME, 
7.        B.BYTES / 1024 / 1024
8.   FROM USER_LOBS A, USER_SEGMENTS B 
9. WHERE A.SEGMENT_NAME = B.SEGMENT_NAME 
10. ORDER BY B.BYTES DESC; 

查询结果：

很显然上述EXCHANGE_LOG占据了50310M的空间，而该表空间总共消耗了63662.796875M,显然问题就出在这个大对象下。

因为该部分数据不能测地删除，故采用如下JOB对重复数据进行提纯处理：

我们通过建exchange_log_history表提取sz_exchange_log中的数据，删除大量的冗余数据

1. create or replace procedure p_job_exchange_log_pure Authid Current_User Is 
2.   num   number; 
3.   days  number; 
4.   v_sql varchar2(3000); 
5. begin 
6.  
7. select trunc(Sysdate- min(create_date)) into days from exchange_log; 
8.  
9.   num := 0; 
10.   while num < days 
11.  
12.   LOOP 
13.     begin 
14.       --建立断点，若异常则回滚到point1之前 
15.       savepoint point1; 
16.       v_sql := 'insert into exchange_log_history('; 
17.       v_sql := v_sql || 
18.                ' Select * from exchange_log a Where (msg_type,msg_code) in (select msg_type,msg_code from exchange_log Where trunc(create_date)=trunc(sysdate-'|| num ||') group by msg_type,msg_code having count(*) >= 1)'; 
19.       v_sql := v_sql || 
20.                ' And rowid in (select min(rowid) from exchange_log Where trunc(create_date)=trunc(sysdate-'|| num || ') group by msg_type,msg_code having count(*)>=1))'; 
21.       execute immediate v_sql; 
22.       commit; 
23.       num := num + 1; 
24.     Exception 
25.       when others then 
26.         rollback to savepoint point1; /*//*异常处理，保存点下面的操作都不会被执行*/ 
27.         return; 
28.     end; 
29.  
30.   end LOOP; 
31.   End p_job_exchange_log_pure; 

 本文转自zylhsy 51CTO博客，原文链接：http://blog.51cto.com/yunlongzheng/635746，如需转载请自行联系原作者