undo表空间爆满了，该如何入手处理。

yiyufeng1986

1.问题描述：线上库有个一含有2亿多条记录的数据，现在是通过DELETE FROM TBSEARCH_CHARTS_PRODUCT_RANKIN WHERE PRDATETIME <= TO_DATE('2012-05-31','yyyy-mm-dd') AND ROWNUM<=10000 来删除数据。但是保留最近的数据，并且每晚用脚本插入新的数据，所以不考虑truncate。
问题就出来了，undo表空间全部耗尽了。这个该如何处理。
2.有人说应该对于那条delete采用了并行处理，所以导致undo中active居高不下，我从哪个视图或表能证明这一点。
3.我问了开发人员，是没删除1万条就commit一次。
数据库版本为10.2.0.4
SQL> show parameter undo;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                             integer     900
undo_tablespace                          string      UNDOTBS1

SQL> select status,sum(bytes)/1024/1024  from dba_undo_extents group by status;
STATUS    SUM(BYTES)/1024/1024
--------- --------------------
UNEXPIRED               1.4375
EXPIRED                  4.875
ACTIVE                98297.25

SQL> select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
   from v$rollstat order by rssize;  2  
       USN      XACTS STATUS          RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024    SHRINKS
---------- ---------- --------------- --------------------- ---------------------- ----------
         1          0 ONLINE                     .000114441             .666130066       4477
        13          0 ONLINE                     .000114441             .427055359        123
        12          0 ONLINE                     .000114441             .318107605        132
         2          0 ONLINE                     .000114441               3.969841       4823
         3          0 ONLINE                     .000114441             3.97557831       8460
         4          0 ONLINE                     .000114441             .249137878        684
         5          0 ONLINE                     .000114441             3.97374725       6966
         6          0 ONLINE                     .000114441             3.98044586       7644
         7          0 ONLINE                     .000114441             3.96159363       7523
         9          0 ONLINE                     .000114441             3.97277069       9448
        10          0 ONLINE                     .000114441             3.95519257      18476
        11          0 ONLINE                     .000114441             3.99375916      24368
        14          0 ONLINE                     .000175476             .991073608        323
         0          0 ONLINE                     .000358582             .000358582          0
         8          1 FULL                       3.99327087             3.99995422       7426

急，请赐教啊。明天在线等。

Maclean Liu(刘相兵

请运行 http://www.oracledatabase12g.com ... nostic-scripts.html 中提供的脚本 ，并上传运行结果

yiyufeng1986

上传undo的诊断结果

yiyufeng1986

我把undo的诊断结果上传到论坛了。帮忙看看^_^

Maclean Liu(刘相兵

系统中存在 大的DML 操作如下

1. ############## Details on Long Run Queries ##############
   
2. 
   
3. 
   
4. SQL ID        SQL Text                                                                         Last Load                              Elapsed Days
   
5. ------------- -------------------------------------------------------------------------------- -------------------------------------- ------------
   
6. 58qn43n42h1kk DELETE FROM TBSEARCH_CHARTS_PRODUCT_RANKIN WHERE PRDATETIME< SYSDATE-8           2012-06-24/00:39:49                         6502973
   
7. 1dk2zft00hzh6 DELETE FROM TBSEARCH_CHARTS_PRODUCT_RANKIN WHERE PRDATETIME <= TO_DATE('2012-05- 2012-06-22/05:45:08                         5014468
   
8. 9bys29hdjymn5 SELECT SUPCATID,SUPCATNAME FROM SUPERMARKET_CAT WHERE LENGTH(SUPCATID)=3         2012-06-26/09:13:59                             198
   
9. cdnakywn4yry4 SELECT "A1"."ID","A1"."AREAID","A1"."PUBDATE","A1"."HOUR","A1"."INFORMATION_CNT" 2012-06-26/23:59:00                           16128
   
10. 8szmwam7fysa3 insert into wri$_adv_objspace_trend_data select timepoint,  space_usage, space_a 2012-06-26/22:00:08                            7118
    
11. agcn4w73kz7js INSERT INTO TBSEARCH__HIS_P (PRKEYWORD,PRPRODUCTCLASS,PRAREACLASS,PRTRADE,PRINFO 2012-06-23/23:15:55   

复制代码

注意以上2个 DELETE FROM TBSEARCH_CHARTS_PRODUCT_RANKIN  语句 消耗了大量undo

yiyufeng1986

那现在目前是不是只能建议开发把那两个语句先停掉吧。如果停掉，但是业务上有需要把将近2亿条数据清掉，只保留最近2周数据，有没有什么办法，来满足这个需求呢。

yobyin

按时间分区，过期的直接turncate 掉 。。省的delele

Maclean Liu(刘相兵

DELETE FROM TBSEARCH_CHARTS_PRODUCT_RANKIN WHERE PRDATETIME< SYSDATE-8   

显然这句是 批量 删除 ， 而不是你说的一条条 删除


对于非分区表 的数据大规模删除：

1.  使用在线重定义    online
2. 利用rowid  划分分区删除        
利用rowid分块实现非分区表的并行update与delete http://www.oracledatabase12g.com ... E4%B8%8Edelete.html           online

3.  仅使用游标 串行rowid删除             online

yiyufeng1986

嗯，谢谢大家了

wzhihua

maclean:
在您提到的第二个方法中，我去http://www.oracledatabase12g.com ... E4%B8%8Edelete.html 看了，并留了疑问，现在把疑问放到这里，请给解释下。
获取rowid分块的SQL脚本中，select * from cnt;这个cnt是个什么？？