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

巡检数据库发现，其中一个数据库表空间一个晚上暴增了9G：表空间状况：<？XML:NAMESPACE PREFIX = O />

	TABLESPACE_NAME	TOTAL_SPACE	USED_SPACE	PCT_FREE
1	SZ_DATA	75774.984375M	63662.796875M	84.02%

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

--大字段
SELECT A.TABLE_NAME,
A.COLUMN_NAME,
B.SEGMENT_NAME,
B.SEGMENT_TYPE,
B.TABLESPACE_NAME,
B.BYTES / 1024 / 1024
FROM USER_LOBS A, USER_SEGMENTS B
WHERE A.SEGMENT_NAME = B.SEGMENT_NAME
ORDER BY B.BYTES DESC;

查询结果：

TABLE_NAME	COLUMN_NAME	SEGMENT_NAME	SEGMENT_TYPE	TABLESPACE_NAME	B.BYTES/1024/1024
EXCHANGE_LOG	ERROR_TRACE	SYS_LOB0000077498C00012$$	LOBSEGMENT	SZ_DATA	50310
SEND_CONTROL	CONTENT	SYS_LOB0000077595C00003$$	LOBSEGMENT	SZ_DATA	2240
ACCESSORY_UPLOAD	FILEDATA	SYS_LOB0000077486C00010$$	LOBSEGMENT	SZ_DATA	488
EXCHANGE_LOG_HISTORY	ERROR_TRACE	SYS_LOB0000133755C00012$$	LOBSEGMENT	SZ_DATA	72
SEND_CONTROL	ERR_TRACE	SYS_LOB0000077595C00008$$	LOBSEGMENT	SZ_DATA	9
FILES	CONTENT	SYS_LOB0000077501C00004$$	LOBSEGMENT	SZ_DATA	2
SEND_CONTROL_HISTORY_2009	CONTENT	SYS_LOB0000133605C00003$$	LOBSEGMENT	SZ_DATA	0.0625
SEND_CONTROL_HISTORY	ERR_TRACE	SYS_LOB0000103089C00008$$	LOBSEGMENT	SZ_DATA	0.0625
MEMOS	CONTENT	SYS_LOB0000077530C00006$$	LOBSEGMENT	SZ_DATA	0.0625
SEND_CONTROL_HISTORY	CONTENT	SYS_LOB0000103089C00003$$	LOBSEGMENT	SZ_DATA	0.0625
NOTICES	CONTENT	SYS_LOB0000077538C00007$$	LOBSEGMENT	SZ_DATA	0.0625
SEND_CONTROL_HISTORY_2009	ERR_TRACE	SYS_LOB0000133605C00008$$	LOBSEGMENT	SZ_DATA	0.0625

很显然上述EXCHANGE_LOG占据了50310M的空间，而该表空间总共消耗了63662.796875M,显然问题就出在这个大对象下。因为该部分数据不能测地删除，故采用如下JOB对重复数据进行提纯处理：
我们通过建exchange_log_history表提取sz_exchange_log中的数据，删除大量的冗余数据

create or replace procedure p_job_exchange_log_pure Authid Current_User Is
num number;
days number;
v_sql varchar2（3000）;
begin
select trunc（Sysdate- min（create_date）） into days from exchange_log;
num := 0;
while num < days
LOOP
begin
--建立断点，若异常则回滚到point1之前
savepoint point1;
v_sql := "insert into exchange_log_history（";
v_sql := v_sql ||
" 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）";
v_sql := v_sql ||
" 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））";
execute immediate v_sql;
commit;
num := num + 1;
Exception
when others then
rollback to savepoint point1; /*//*异常处理，保存点下面的操作都不会被执行*/
return;
end;
end LOOP;
End p_job_exchange_log_pure;

Linux Oracle 10.2.0.1 lsnrctl无法启动Oracle log_archive_dest_1 未指定导致flash_recovery_area引发数据库挂起相关资讯 Oracle基础教程

Oracle块编程返回结果集详解（11/10/2013 10:45:58）
Oracle基础教程之设置系统全局区（08/22/2013 14:24:00）
Oracle基础教程知识点总结（06/18/2013 07:43:32）

Oracle基础教程之tkprof程序详解（10/22/2013 11:49:50）
Oracle基础教程之sqlplus汉字乱码（07/18/2013 16:30:00）
Oracle 管理之 Linux 网络基础（02/16/2013 18:37:35）

本文评论查看全部评论（0）

表情：姓名：匿名字数

<button class="layui-btn layui-bg-purple" id="dofav">收藏该网址</button>
        </div>
    </div>
</div>
<div class="copyright">
    <div id="footbar">
        版权所有©石家庄振强科技有限公司2024 <a href="https://beian.miit.gov.cn" target="_blank">冀ICP备08103738号-5</a> <a href="/storage/sitemap.xml">网站地图</a>
    </div>
</div>
<script> var _mtj = _mtj || [];
(function () {
    var mtj = document.createElement("script");
    mtj.src = "https://node12.aizhantj.com:21233/tjjs/?k=p2tceukth5c";
    var s = document.getElementsByTagName("script")[0];
    s.parentNode.insertBefore(mtj, s);
})(); </script>
<script src="/static/lib/layui/layui.js"></script>
<script src="/static/lib/jquery/jquery.js"></script>
<script src="/static/lib/ajax.js"></script>
<script>
    layui.use(function () {
        var layer = layui.layer;
        var $ = layui.jquery;
        $("#dofav").click(function () {
            var artid = $(".article").attr("artid");
            var params={
                artid:artid,
                addtype:'escdns_article',
                t:Math.random()
            };
            if(artid>0){
                ajax.request({
                    method: "/index/article/addfavorite", type: "post", callback: function (res) {
                        layer.msg(res.msg);
                    }
                },params);
            }
        })
    })
</script>
</body>
</html>