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

1. 故障定位处理过程： 
2. 1、发现监控系统报告USERS表空间99%使用率 
3. 我们通过如下语句查USERS表空间有哪些对象 
4. select owner,segment_name,segment_type,bytes from dba_segments a where a.tablespace_name='USERS' 
5. …………………… 
6. 8   SNDFC   SYS_LOB0000077141C00012$$   LOBSEGMENT  18817744896 
7. 17  SNDFC   SYS_LOB0000094212C00003$$   LOBSEGMENT  10583277568 
8. 15  SNDFC   SYS_LOB0000092081C00012$$   LOBSEGMENT  2824863744 
9. 13  SNDFC   SYS_LOB0000077219C00003$$   LOBSEGMENT  2618294272 
10. 12  SNDFC   SYS_LOB0000077219C00008$$   LOBSEGMENT  38797312 
11. 24  SNDFC   SYS_IL0000077219C00003$$    LOBINDEX    26214400 
12. 19  SNDFC   SYS_IL0000077141C00012$$    LOBINDEX    8388608 
13. 18  SNDFC   SYS_LOB0000077029C00008$$   LOBSEGMENT  4194304 
14. 14  SNDFC   SYS_LOB0000077192C00007$$   LOBSEGMENT  3145728 
15. 23  SNDFC   SYS_IL0000077219C00008$$    LOBINDEX    131072 
16. 28  SNDFC   SYS_IL0000094212C00003$$    LOBINDEX    131072 
17. 21  SNDFC   SYS_IL0000077227C00008$$    LOBINDEX    65536 
18. 22  SNDFC   SYS_IL0000077227C00004$$    LOBINDEX    65536 
19. 26  SNDFC   SYS_IL0000092081C00012$$    LOBINDEX    65536 
20. 27  SNDFC   SYS_IL0000094212C00008$$    LOBINDEX    65536 
21. 25  SNDFC   SYS_IL0000077192C00007$$    LOBINDEX    65536 
22. 29  SNDFC   SYS_IL0000077029C00008$$    LOBINDEX    65536 
23. 20  SNDFC   SYS_IL0000077087C00005$$    LOBINDEX    65536 
24. 9   SNDFC   SYS_LOB0000077087C00005$$   LOBSEGMENT  65536 
25. 16  SNDFC   SYS_LOB0000094212C00008$$   LOBSEGMENT  65536 
26. 10  SNDFC   SYS_LOB0000077227C00008$$   LOBSEGMENT  65536 
27. 11  SNDFC   SYS_LOB0000077227C00004$$   LOBSEGMENT  65536 
28.  
29. 很惊奇的发现里面竟然没有数据表，显然SYS_LOB0000077141C00012$$等几个大的对象占据了存储空间。 
30. 因为属性是LOBSEGMENT，马上想到是大对象的表发生了大量的数据增长。 
31.  
32. 于是动用如下SQL语句，查处大字段对象： 
33. SELECT A.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.   FROM USER_LOBS A, USER_SEGMENTS B 
42. WHERE A.SEGMENT_NAME = B.SEGMENT_NAME 
43. ORDER BY B.BYTES DESC; 
44.  
45. 查询结果如下： 
46. TABLE_NAME          COLUMN_NAME SEGMENT_NAME            SEGMENT_TYPE    TABLESPACE_NAME     B.BYTES/1024/1024 
47. SNDFC_EXCHANGE_LOG_INFO     ERROR_TRACE SYS_LOB0000077141C00012$$   LOBSEGMENT  USERS           17946 
48. SNDFC_SEND_CONTROL_HISTORY  CONTENT     SYS_LOB0000094212C00003$$   LOBSEGMENT  USERS           10093 
49. TMP_SNDFC_EXCHANGE_LOG_INFO ERROR_TRACE SYS_LOB0000125221C00012$$   LOBSEGMENT  IN_SNDFC_DATA       5857 
50. SNDFC_EXCHANGE_LOG_HISTORY  ERROR_TRACE SYS_LOB0000092081C00012$$   LOBSEGMENT  USERS           2694 
51. SNDFC_SEND_CONTROL      CONTENT     SYS_LOB0000077219C00003$$   LOBSEGMENT  USERS           2497 
52. SNDFC_SEND_CONTROL      ERR_TRACE   SYS_LOB0000077219C00008$$   LOBSEGMENT  USERS           37 
53. SNDFC_COM_RESOURCE      FILE_BLOB   SYS_LOB0000077029C00008$$   LOBSEGMENT  USERS           4 
54. SNDFC_NOTICE_FILE       DOC_CONTENT SYS_LOB0000077192C00007$$   LOBSEGMENT  USERS           3 
55. BIN$pIfKdb4Sv0LgQBqsDaM1Tg==$0  ERROR_TRACE SYS_LOB0000125215C00012$$   LOBSEGMENT  IN_SNDFC_DATA       0.6875 
56. SNDFC_AUTO_PORT_BARRIER_LOG EXCEPTION_STACK SYS_LOB0000102643C00011$$   LOBSEGMENT  IN_SNDFC_DATA       0.375 
57. TMP_SAVE_TABLEDDL       TABLE_SQL   SYS_LOB0000117696C00002$$   LOBSEGMENT  IN_SNDFC_DATA       0.125 
58. TMP_SAVE_INDEXDDL       INDEX_SQL   SYS_LOB0000117779C00002$$   LOBSEGMENT  IN_SNDFC_DATA       0.0625 
59. SNDFC_SEND_CONTROL_HISTORY  ERR_TRACE   SYS_LOB0000094212C00008$$   LOBSEGMENT  USERS           0.0625 
60. SNDFC_SEND_CONTROL_HIS      CONTENT     SYS_LOB0000077227C00004$$   LOBSEGMENT  USERS           0.0625 
61. SNDFC_SEND_CONTROL_HIS      ERR_TRACE   SYS_LOB0000077227C00008$$   LOBSEGMENT  USERS           0.0625 
62. SNDFC_ENTRY_RESOURCE        FILE_BLOB   SYS_LOB0000077087C00005$$   LOBSEGMENT  USERS           0.0625 
63. BIN$pHyd9j2iK0vgQBqsDaNWjQ==$0  ERROR_TRACE SYS_LOB0000124975C00012$$   LOBSEGMENT  IN_SNDFC_DATA       0.0625 
64.  
65. 根据以上查询，显然SNDFC_EXCHANGE_LOG_INFO表上的SYS_LOB0000077141C00012$$对象作用了17G的空间。 
66. 正巧应用值班人员反馈某业务的数据交换程序出错，而出错的交换表中正巧有大字段对象，再一确认就是SNDFC_EXCHANGE_LOG_INFO表。 
67. 查该表总记录数和事件发生当天记录数： 
68. SQL> Select Count(*) From  sndfc_exchange_log_info t Where to_char(modi_date,'yyyymmdd')='20110529'; 
69.  
70.   COUNT(*) 
71. ---------- 
72.    1492157 
73.  
74. SQL> Select Count(*) From  sndfc_exchange_log_info t; 
75.  
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表空间备库相应目录就会不足， 昨天为了处理备库相应目录不足问题已经颇费周折，在此简单回顾下。