Oracle存储过程执行一段时间后停止，在线请教！！

我的存储过程执行会就会停止
不是终止  感觉就象超时一样   我info_sort_list有3千多万数据 进行拆分
希望大家能帮个忙  感激不尽附存储过程
create or replace procedure site_data_process_row_proc(p_sortList_Row info_sort_list%rowtype)
 as
        v_insertsqlstr varchar2(256);
        v_updatesqlstr varchar2(256);
        v_ip_address_id varchar2(64);
        v_ip_num int;                     --ip地址的数组形式
        v_is_insert_sql varchar2(256);
        v_count_num int;
        v_have_table int;             --查询表是否存在
        v_create_sql varchar2(1024);   --发生错误是,用来建表的SQL语句
        v_month_str varchar2(10);
begin
DECLARE CURSOR stat_list_cur
      IS
      SELECT list.ipaddress_id,list.ipnum  FROM stat_list list WHERE list.session_id = p_sortList_Row.session_id;
    
--使用游标来对v_ip_address_id 和 v_ip_num 进行赋值  不可使用直接赋值方式  防止查询不到时产生异常终止程序
--因为info_sort_list表内有些数据不完整 所以处理时需要注意
        begin
           open stat_list_cur;
         
           FETCH stat_list_cur into v_ip_address_id,v_ip_num;           close stat_list_cur;
      end;
           
--得到该条数据的日期       
      BEGIN
           v_month_str := TO_CHAR(to_date(p_sortList_Row.View_Date,'yyyy-mm-dd hh24:mi:ss'),'yyyy_mm');      EXCEPTION
               WHEN OTHERS THEN
               return;
               
      END;
--原处理方法在查询不到时会产生异常 抛弃
/*
--在stat_list表中查询ip_address_id和ipnum,通过session_id
SELECT list.ipaddress_id,list.ipnum
       INTO
       v_ip_address_id,v_ip_num
FROM stat_list list
WHERE list.session_id = p_sortList_Row.session_id;*/--对查询后得到的ip地址于ip_num进行判断  如果数据不完整则不对该条数据进行处理  直接返回
if (v_ip_address_id is NULL) then
   return;
end if;
if (v_ip_num is NULL) then
   return;
end if;--查询一小时之内是否从新登录过
v_is_insert_sql := 'SELECT COUNT(1) FROM stat_site_total_'|| v_month_str||
                                  ' WHERE site_type='''|| p_sortList_Row.SITE_TYPE ||
                                        ''' AND site_id='|| p_sortList_Row.site_id ||
                                        '   AND total_date='''||to_char(to_date(p_sortList_Row.view_DATE,'yyyy-mm-dd hh24:mi:ss'),'yyyymmddhh24') ||
                                        ''' AND ipnum='|| v_ip_num;--执行插入时的语句
v_insertsqlstr := 'insert into STAT_SITE_TOTAL_' || v_month_str ||
          ' values(''' ||
           p_sortList_Row.SITE_TYPE ||''','||
           p_sortList_Row.SITE_ID || ','''||
           to_char(to_date(p_sortList_Row.view_DATE,'yyyy-mm-dd hh24:mi:ss'),'yyyymmddhh24') || ''','||
           p_sortList_Row.site_count || ','||
           '1,'''||
           p_sortList_Row.STANDBY1 ||''','''||
           v_ip_address_id || ''','||
           v_ip_num || ')'
           ;
--执行更新的语句
v_updatesqlstr :=  ' UPDATE STAT_SITE_TOTAL_' ||v_month_str ||
                 ' SET ' ||                 ' page_brow_count=page_brow_count+'|| p_sortList_Row.site_count || ','||
                 ' acce_count_total=acce_count_total+1,'||
                 ' standby1='''||p_sortList_Row.STANDBY1|| ''''||                 ' WHERE site_type='''|| p_sortList_Row.SITE_TYPE ||
                                        ''' AND site_id='|| p_sortList_Row.site_id ||
                                        '   AND total_date='''||to_char(to_date(p_sortList_Row.view_DATE,'yyyy-mm-dd hh24:mi:ss'),'yyyymmddhh24') ||
                                        ''' AND ipnum='|| v_ip_num ;
/*v_updatesqlstr :=  ' UPDATE STAT_SITE_TOTAL_' ||v_month_str ;
v_updatesqlstr :=  v_updatesqlstr||' SET ' ;v_updatesqlstr :=   v_updatesqlstr|| ' page_brow_count=page_brow_count+'|| p_sortList_Row.site_count || ',';
v_updatesqlstr :=   v_updatesqlstr||  ' acce_count_total=acce_count_total+1,';
v_updatesqlstr :=    v_updatesqlstr||    ' standby1='''||p_sortList_Row.STANDBY1|| '''';v_updatesqlstr :=   v_updatesqlstr||     ' WHERE site_type='''|| p_sortList_Row.SITE_TYPE ;
v_updatesqlstr :=   v_updatesqlstr||        ''' AND site_id='|| p_sortList_Row.site_id ;
 v_updatesqlstr :=   v_updatesqlstr||    '   AND total_date='''||to_char(to_date(p_sortList_Row.view_DATE,'yyyy-mm-dd hh24:mi:ss'),'yyyymmddhh24') ;
  v_updatesqlstr :=   v_updatesqlstr||           ''' AND ipnum='|| v_ip_num      ;*/           execute immediate v_is_insert_sql into v_count_num;
      --     EXEC_res_SQL_STR(v_is_insert_sql,v_count_num);            --用于判断
           --1、此表是否存在 如果不存在出现异常后面进行处理
           --2、如果返回值为0，则表示该表存在且该用户1小时内为登陆过，执行 exec_sql_str(v_insertsqlstr);将数据直接插入到对应表
           --3、如果返回值不为0，则表示该用户1小时内已登陆过，直接更新结果。           if v_count_num = 0 then
              execute immediate v_insertsqlstr;
           else
              execute immediate v_updatesqlstr;
           end if;
--==================================================================================================
EXCEPTION
    WHEN OTHERS THEN--DBMS_OUTPUT.PUT_LINE('sortListID =:                '|| p_sortList_Row.Info_Sort_Id);    SELECT COUNT(1) into v_have_table FROM user_tables WHERE table_name = 'STAT_SITE_TOTAL_' || v_month_str;
--查询某表是否已存在，将结果插入v_hava_table变量，如果v_hava_table=0 则表示不存在
    v_create_sql := 'create table STAT_SITE_TOTAL_' ||v_month_str ||             --建表语句
                                '(
                                  SITE_TYPE        VARCHAR2(255),
                                  SITE_ID          NUMBER(10),
                                  TOTAL_DATE       VARCHAR2(10),
                                  PAGE_BROW_COUNT  NUMBER(10),
                                  ACCE_COUNT_TOTAL NUMBER(10),
                                  STANDBY1         VARCHAR2(255),
                                  IPADDRESS_ID     VARCHAR2(17),
                                  IPNUM            NUMBER(20)
                                )';    --如果表不存在
    if v_have_table = 0 then
         execute immediate v_create_sql;           --执行建表语句  
         --!!!执行前应该确认 该用户是否用建表权限 若没有先使用 dba用户  执行 grant create table to user命令
         site_data_process_row_proc(p_sortList_Row);             --重新执行本存储过称，再对数据进行插入
    else
        --如果有其他异常 ，对该条数据插入到错误表中 记录，方便事后进行处理
         insert into process_error  values(p_sortList_Row.info_sort_id,sysdate,1,'');
         commit;
     end if;
--==================================================================================================end;create or replace procedure site_data_process_proc(bgnID NUMBER,endId Number)
AS
v_row info_sort_list%rowtype; 
                              --声明变量行 v-row 类型为info_sort_list表的？
begin
     DECLARE CURSOR site_cursor                               --声明游标，把info_sort_list表里的所有数据放入到site_cursor游标中去
      IS
--      select * FROM info_sort_list list where list.info_sort_id>10965608 and list.info_sort_id<=10970642 order by list.info_sort_id asc;
     select * FROM info_sort_list list  where list.info_sort_id>=bgnID and list.info_sort_id<=endId order by list.info_sort_id asc;
      begin
           open site_cursor;                                 --打开游标，对每行进行处理
           loop
           FETCH site_cursor into v_row;                     --将每行赋值给变量v_row
           EXIT WHEN site_cursor %NOTFOUND;                  --如果游标已经循环到最后一行结束  退？                
           BEGIN
                site_data_process_row_proc(v_row);           --使用site_data_process_row_proc存储过程对每行数据进行处？ 
                insert into process_error values(v_row.info_sort_id,sysdate,9,'Doing');         
                commit;
                EXCEPTION
                         WHEN OTHERS THEN
                         insert into process_error values(v_row.info_sort_id,sysdate,0,'');        --如果出现一场插入错误表
                       commit;
                       close site_cursor;
                       
                       return;
                END;
           end loop;
           close site_cursor;
      end;
end;

解决方案 »

  1.   

    从逻辑上看是“循环”里面套“循环”，反复的调用存储过程，就像进入一个无底洞，出不来了。
    你跳出“循环”的语句是RETURN吧？你测试有执行到这个语句了吗？
      

  2.   

    兄弟  我这个执行是没问题的 可以正常执行也可有结果 
    就是执行一段时间会卡住
    是不是oracle需要设置什么时间？？
    感觉存储过程没问题  一般执行2小时后会卡死  
      

  3.   


      --得到该条数据的日期      
      BEGIN
        v_month_str := TO_CHAR(to_date(p_sortList_Row.View_Date,
                                       'yyyy-mm-dd hh24:mi:ss'),
                               'yyyy_mm');
      EXCEPTION
        WHEN OTHERS THEN
          return;
      END;
    是否应该为：    v_month_str := TO_CHAR(to_date(p_sortList_Row.View_Date,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd');两个存贮过程可以分别调试，可用debug一步一步来。
      

  4.   

    从代码上来看，没有明显的逻辑错误。“死”的时候，从v$session_wait查一上，看这个 session在干什么
      

  5.   


    查了吗？卡住的时候，这个session有哪些等待事件？
      

  6.   


    1.可以通过alter system kill session 'sid,serial#'来杀掉会话。
     
    2.
    SELECT /*+ rule */ s.username, 
    decode(l.type,'TM','TABLE LOCK', 
                  'TX','ROW LOCK', 
                  NULL) LOCK_LEVEL, 
    o.owner,o.object_name,o.object_type, 
    s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser 
    FROM v$session s,v$lock l,dba_objects o 
    WHERE l.sid = s.sid 
    AND l.id1 = o.object_id(+) 
    AND s.username is NOT NULL 3.如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待。以下的语句可以查询到谁锁了表，而谁在等待。 
    SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name, 
           o.owner,o.object_name,o.object_type,s.sid,s.serial# 
    FROM v$locked_object l,dba_objects o,v$session s 
    WHERE l.object_id=o.object_id 
    AND l.session_id=s.sid 
    ORDER BY o.object_id,xidusn DESC 
      

  7.   

    不好意思  楼上的朋友我没差session
    不过每次都是2小时死不会是锁死问题吧  而且时间都一样
    这样  我这次查查看
    还有oracle有哪里设置超时的地方吗
      

  8.   

    1 140 37202 db file sequential read file# 4 00000004 block# 148649 000244A9 blocks 1 00000001 1740759767 8 User I/O -1 0 WAITED SHORT TIME
    2 142 32 pipe get handle address 738458108 2C03F9FC buffer length 4096 00001000 timeout 3600 00000E10 2723168908 6 Idle 0 471 WAITING
    3 143 125 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 00000001 0 00 2723168908 6 Idle 0 365 WAITING
    4 144 9806 SQL*Net message from client driver id 1952673792 74637000 #bytes 1 00000001 0 00 2723168908 6 Idle 0 59907 WAITING
    5 145 12682 SQL*Net message from client driver id 1952673792 74637000 #bytes 1 00000001 0 00 2723168908 6 Idle 0 59893 WAITING
    6 146 247 SQL*Net message from client driver id 1952673792 74637000 #bytes 1 00000001 0 00 2723168908 6 Idle 0 314 WAITING
    7 149 1403 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 00000001 0 00 2723168908 6 Idle 0 309 WAITING
    8 150 248 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 00000001 0 00 2723168908 6 Idle 0 0 WAITING
    9 151 57 SQL*Net message to client driver id 1413697536 54435000 #bytes 1 00000001 0 00 2000153315 7 Network -1 0 WAITED SHORT TIME
    10 152 3145 SQL*Net message from client driver id 1952673792 74637000 #bytes 1 00000001 0 00 2723168908 6 Idle 0 70823 WAITING
    11 154 409 rdbms ipc message timeout 500 000001F4 0 00 0 00 2723168908 6 Idle 0 93 WAITING
    12 155 3527 SQL*Net message from client driver id 1952673792 74637000 #bytes 1 00000001 0 00 2723168908 6 Idle 0 71033 WAITING
    13 159 119 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 00000001 0 00 2723168908 6 Idle 0 471 WAITING
    14 160 792 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 00000001 0 00 2723168908 6 Idle 0 309 WAITING
    15 161 8 rdbms ipc message timeout 100 00000064 0 00 0 00 2723168908 6 Idle 0 71470 WAITING
    16 162 4261 rdbms ipc message timeout 300 0000012C 0 00 0 00 2723168908 6 Idle 0 96 WAITING
    17 163 22 rdbms ipc message timeout 180000 0002BF20 0 00 0 00 2723168908 6 Idle 0 57371 WAITING
    18 164 6127 smon timer sleep time 300 0000012C failed 0 00 0 00 2723168908 6 Idle 0 89 WAITING
    19 165 48279 rdbms ipc message timeout 300 0000012C 0 00 0 00 2723168908 6 Idle 0 2 WAITING
    20 166 18581 rdbms ipc message timeout 110 0000006E 0 00 0 00 2723168908 6 Idle 0 0 WAITING
    21 167 1846 rdbms ipc message timeout 300 0000012C 0 00 0 00 2723168908 6 Idle 0 54 WAITING
    22 168 13 rdbms ipc message timeout 300 0000012C 0 00 0 00 2723168908 6 Idle 0 110 WAITING
    23 169 182 rdbms ipc message timeout 300 0000012C 0 00 0 00 2723168908 6 Idle 0 2688 WAITING
    24 170 10 pmon timer duration 300 0000012C 0 00 0 00 2723168908 6 Idle 0 71813 WAITINGselect * from v$session_wait
      

  9.   

    0)看一下alert文件里有什么错误没有
      D:\oracle\product\10.2.0\admin\study\bdump\alert_study.log（你的因数据库版本和安装路径会有不同）1）查看v$session_wait,看在等待什么事件。
       你列出一大堆的来，但是又没有说明你执行sp的那个session的id
    2) 查看有没有锁在等待
       select * from dba_waiters;
       select * from v$locked_objects;根据你目前的情况来看，我知道的就这些了。
      

  10.   

    朋友们 麻烦注意下我说的
    不是什么死 而是不管我执行什么存储过程 只要到2小时就死  哪怕一直insert一个表都是一样的
    谁知道哪里调超时时间啊
    我的是linux下的oracle   听别人说调connect——time不知道在那里改  知道的麻烦告诉下 谢谢
      

  11.   

    可以以此判断是Oracle问题，还是网络问题：
    1.ping 下去；
    2.每五分钟左右连接一下Oracle。如果ping先断，那么Oracle没问题，是网络问题。这主要要考虑，防火墙，病毒什么的。
    如果ping一直通，那么是Oracle方面的问题。
      

  12.   


    我执行了 这个存储过程
    create or replace procedure TestHours is
    begin
      FOR a IN 1..999999999
      LOOP
         dbms_lock.sleep(5);
         insert into hptest values(a,sysdate);
         commit;
      END LOOP;
    end TestHours;
    执行结果也是2小时就不走了这个给大家看看  肯定是哪里配置问题  麻烦各位朋友帮忙解决下 谢谢
    select * from hptest t
    order by id desc  --2008-11-1 0:10:26select * from hptest t
    order by id asc  --2008-10-31 22:10:29
      

  13.   

    建议LZ按blueskywhite、codearts的方法先确定问题出在哪儿，然后在决定如何解决。一味地执行存储过程，然后在2小时时卡死，这只是表面现象，并不是发生问题的原因。
    首先：查看是否是oracle的问题
    其次：死的时间oracle在做什么
    最后：找到具体的原因，对症下药！
      

  14.   

    我执行了 这个存储过程 
    create or replace procedure TestHours is 
    begin 
      FOR a IN 1..999999999 
      LOOP 
        dbms_lock.sleep(5); 
        insert into hptest values(a,sysdate); 
        commit; 
      END LOOP; 
    end TestHours; 
    执行结果也是2小时就不走了 这个给大家看看  肯定是哪里配置问题  麻烦各位朋友帮忙解决下 谢谢 
    select * from hptest t 
    order by id desc  结束时间--2008-11-1 0:10:26 select * from hptest t 
    order by id asc  开始时间--2008-10-31 22:10:29很明显执行2小时就不动了  我感觉是设置问题 谁知道改哪里设置啊？？？