一次带大字段表记录暴增（LOBSEGMENT）引发的悲剧

原创作品，允许转载，转载时请务必以超链接形式标明文章 原始出处 、作者信息和本声明。否则将追究法律责任。 http://yunlongzheng.blog.51cto.com/788996/578973

事件起因：ORACLE主库进行例行周期性停机后应用相关人员确认数据交换程序时未发现交换异常，

导致该交换程序连接其中一个数据库成功，连接另外一个数据库失败，因此不断的向连接成功的 数据库的状态控制表中写入交换异常的数据，而控制表中有CLOB字段记录所有的出错信息。 该程序在周末跑了约24H，导致产生120W条垃圾数据，耗费表空间10G以上。

故障定位处理过程：

1、发现监控系统报告USERS表空间99%使用率

我们通过如下语句查USERS表空间有哪些对象

1. 故障定位处理过程：
2. 1、发现监控系统报告USERS表空间99%使用率
3. 我们通过如下语句查USERS表空间有哪些对象
4. selectowner,segment_name,segment_type,bytesfromdba_segmentsawherea.tablespace_name='USERS'
5. ……………………
6. 8SNDFCSYS_LOB0000077141C00012$$LOBSEGMENT18817744896
7. 17SNDFCSYS_LOB0000094212C00003$$LOBSEGMENT10583277568
8. 15SNDFCSYS_LOB0000092081C00012$$LOBSEGMENT2824863744
9. 13SNDFCSYS_LOB0000077219C00003$$LOBSEGMENT2618294272
10. 12SNDFCSYS_LOB0000077219C00008$$LOBSEGMENT38797312
11. 24SNDFCSYS_IL0000077219C00003$$LOBINDEX26214400
12. 19SNDFCSYS_IL0000077141C00012$$LOBINDEX8388608
13. 18SNDFCSYS_LOB0000077029C00008$$LOBSEGMENT4194304
14. 14SNDFCSYS_LOB0000077192C00007$$LOBSEGMENT3145728
15. 23SNDFCSYS_IL0000077219C00008$$LOBINDEX131072
16. 28SNDFCSYS_IL0000094212C00003$$LOBINDEX131072
17. 21SNDFCSYS_IL0000077227C00008$$LOBINDEX65536
18. 22SNDFCSYS_IL0000077227C00004$$LOBINDEX65536
19. 26SNDFCSYS_IL0000092081C00012$$LOBINDEX65536
20. 27SNDFCSYS_IL0000094212C00008$$LOBINDEX65536
21. 25SNDFCSYS_IL0000077192C00007$$LOBINDEX65536
22. 29SNDFCSYS_IL0000077029C00008$$LOBINDEX65536
23. 20SNDFCSYS_IL0000077087C00005$$LOBINDEX65536
24. 9SNDFCSYS_LOB0000077087C00005$$LOBSEGMENT65536
25. 16SNDFCSYS_LOB0000094212C00008$$LOBSEGMENT65536
26. 10SNDFCSYS_LOB0000077227C00008$$LOBSEGMENT65536
27. 11SNDFCSYS_LOB0000077227C00004$$LOBSEGMENT65536
29. 很惊奇的发现里面竟然没有数据表，显然SYS_LOB0000077141C00012$$等几个大的对象占据了存储空间。
30. 因为属性是LOBSEGMENT，马上想到是大对象的表发生了大量的数据增长。
32. 于是动用如下SQL语句，查处大字段对象：
33. SELECTA.TABLE_NAME,
34. A.COLUMN_NAME,
35. B.SEGMENT_NAME,
36. B.SEGMENT_TYPE,
37. B.TABLESPACE_NAME,
38. B.BYTES/1024/1024,
39. B.BLOCKS,
40. B.EXTENTS
41. FROMUSER_LOBSA,USER_SEGMENTSB
42. WHEREA.SEGMENT_NAME=B.SEGMENT_NAME
43. ORDERBYB.BYTESDESC;
45. 查询结果如下：
46. TABLE_NAMECOLUMN_NAMESEGMENT_NAMESEGMENT_TYPETABLESPACE_NAMEB.BYTES/1024/1024
47. SNDFC_EXCHANGE_LOG_INFOERROR_TRACESYS_LOB0000077141C00012$$LOBSEGMENTUSERS17946
48. SNDFC_SEND_CONTROL_HISTORYCONTENTSYS_LOB0000094212C00003$$LOBSEGMENTUSERS10093
49. TMP_SNDFC_EXCHANGE_LOG_INFOERROR_TRACESYS_LOB0000125221C00012$$LOBSEGMENTIN_SNDFC_DATA5857
50. SNDFC_EXCHANGE_LOG_HISTORYERROR_TRACESYS_LOB0000092081C00012$$LOBSEGMENTUSERS2694
51. SNDFC_SEND_CONTROLCONTENTSYS_LOB0000077219C00003$$LOBSEGMENTUSERS2497
52. SNDFC_SEND_CONTROLERR_TRACESYS_LOB0000077219C00008$$LOBSEGMENTUSERS37
53. SNDFC_COM_RESOURCEFILE_BLOBSYS_LOB0000077029C00008$$LOBSEGMENTUSERS4
54. SNDFC_NOTICE_FILEDOC_CONTENTSYS_LOB0000077192C00007$$LOBSEGMENTUSERS3
55. BIN$pIfKdb4Sv0LgQBqsDaM1Tg==$0ERROR_TRACESYS_LOB0000125215C00012$$LOBSEGMENTIN_SNDFC_DATA0.6875
56. SNDFC_AUTO_PORT_BARRIER_LOGEXCEPTION_STACKSYS_LOB0000102643C00011$$LOBSEGMENTIN_SNDFC_DATA0.375
57. TMP_SAVE_TABLEDDLTABLE_SQLSYS_LOB0000117696C00002$$LOBSEGMENTIN_SNDFC_DATA0.125
58. TMP_SAVE_INDEXDDLINDEX_SQLSYS_LOB0000117779C00002$$LOBSEGMENTIN_SNDFC_DATA0.0625
59. SNDFC_SEND_CONTROL_HISTORYERR_TRACESYS_LOB0000094212C00008$$LOBSEGMENTUSERS0.0625
60. SNDFC_SEND_CONTROL_HISCONTENTSYS_LOB0000077227C00004$$LOBSEGMENTUSERS0.0625
61. SNDFC_SEND_CONTROL_HISERR_TRACESYS_LOB0000077227C00008$$LOBSEGMENTUSERS0.0625
62. SNDFC_ENTRY_RESOURCEFILE_BLOBSYS_LOB0000077087C00005$$LOBSEGMENTUSERS0.0625
63. BIN$pHyd9j2iK0vgQBqsDaNWjQ==$0ERROR_TRACESYS_LOB0000124975C00012$$LOBSEGMENTIN_SNDFC_DATA0.0625
65. 根据以上查询，显然SNDFC_EXCHANGE_LOG_INFO表上的SYS_LOB0000077141C00012$$对象作用了17G的空间。
66. 正巧应用值班人员反馈某业务的数据交换程序出错，而出错的交换表中正巧有大字段对象，再一确认就是SNDFC_EXCHANGE_LOG_INFO表。
67. 查该表总记录数和事件发生当天记录数：
68. SQL>SelectCount(*)Fromsndfc_exchange_log_infotWhereto_char(modi_date,'yyyymmdd')='20110529';
70. COUNT(*)
71. ----------
72. 1492157
74. SQL>SelectCount(*)Fromsndfc_exchange_log_infot;
76. COUNT(*)
77. ----------
78. 2043454
79. 该表保存一个月的记录，总记录数量204W，但该天的记录数量达到149W，显然存在问题。
80. 怎么处理这149W条记录，必须新建一张临时表sndfc_exchange_log_info_new，然后把需要的记录移动到该表，
81. 把sndfc_exchange_log_info重命名为sndfc_exchange_log_info_old
82. 再TRUNCATE原先的sndfc_exchange_log_info_old表释放空间,然后把sndfc_exchange_log_info_new重命名
83. 为sndfc_exchange_log_info（注意主键和索引等约束）。

其实表数据的删除还是比较容易的，但是最悲剧的是我们的DATAGUARD环境磁盘非常紧缺。 为了移动sndfc_exchange_log_info的数据，必须增大USERS表空间，而增大USERS表空间备库相应目录就会不足， 昨天为了处理备库相应目录不足问题已经颇费周折，在此简单回顾下