oracle存储过程执行一段时间后，就会死掉，没有抱错，请朋友们帮我看看是什么原因，废话不多说，上存储过程。
这个存储过程的调用过程是，服务器程序调用存储过程A，然后存储过程A再调用这个存储过程DW_NEWY。在这个存储过程中调用了四个数据库连接，已经把数据库连接改为了10，刚开始可以调用，可是调用之后，一般在24小时后，就会死掉，没有任何抱错，然后整个存储过程包括存储过程A都不再运行。请朋友们帮我看看是什么原因。create or replace PROCEDURE DW_NEWY_ALL -- 联通员工激活自己状态
(From_Tel_ IN VARCHAR2, Smsmsg_ IN VARCHAR2,Rowid_ IN VARCHAR2,Reserved_ IN VARCHAR2) AS
Begin
Declare
temp1_ number(5);
temp2_ number(5);
temp3_ number(5);
temp4_ number(5);
status_ number(20);
dw_stat_ varchar2(2);
feetype_ varchar2(2);
ceshi_ varchar2(2);
smsmsg varchar2(10);
zdfee_ varchar2(8);
fee_standard_id_ varchar2(30);
sq_co_sim_ varchar2(11);
Msgcontent varchar2(360);
Begin
begin
temp1_ := existsan(from_tel_);--判断在188数据库是否存在
temp2_ := existhn(from_tel_);--判断在15数据库是否存在if (temp1_ =2 ) then --在188上激活
begin
select status, dw_stat ,fee_type,ceshi into status_ ,dw_stat_,feetype_,ceshi_ from t_user_reg@SANHEYI188 where reg_sim=from_tel_;
select sq_co_sim into sq_co_sim_ from t_sq_user@SANHEYI188 where reg_sim=from_tel_ and rownum<2;
if(status_=23) then
select tc.id into fee_standard_id_ from t_user_a@FEE188 tu, t_tc_a@FEE188 tc where tu.fee_standard_id=tc.fee_name and tc.buss_type='T_QYGNJ' and tu.fee_user_tel=sq_co_sim_;
elsif(status_ = 24) then
select tc.id into fee_standard_id_ from t_user_a@FEE188 tu, t_tc_a@FEE188 tc where tu.fee_standard_id=tc.fee_name and tc.buss_type='U_QYGN' and tu.fee_user_tel=sq_co_sim_; elsif(status_ = 25) then
select tc.id into fee_standard_id_ from t_user_a@FEE188 tu, t_tc_a@FEE188 tc where tu.fee_standard_id=tc.fee_name and tc.buss_type='M_QYGN' and tu.fee_user_tel=sq_co_sim_; elsif(status_ = 22) then
select tc.id into fee_standard_id_ from t_user_a@FEE188 tu, t_tc_a@FEE188 tc where tu.fee_standard_id=tc.fee_name and tc.buss_type='J_QYGN' and tu.fee_user_tel=sq_co_sim_;
end if;
insert into t_user_zd@FEE188(id,fee_user_tel,buss_type,add_taocan_time,fee_standard_add_time,fee_standard_person,fee_standard_id) values
(t_fee_users.nextval@FEE188,from_tel_,0,sysdate,sysdate,'admin',fee_standard_id_);
update t_user_reg@SANHEYI188 set dw_stat='01' where reg_sim=from_tel_;
temp3_:=1;
exception
when no_data_found then
begin
temp3_:=2;
end;
when others then
temp3_:=3;
end;
end if;if ( temp2_= 2) then --在15上激活
begin
select status, dw_stat ,fee_type,ceshi into status_ ,dw_stat_,feetype_,ceshi_ from t_user_reg@map15 where reg_sim=From_Tel_;
select sq_co_sim into sq_co_sim_ from t_sq_user@map15 where reg_sim=From_Tel_ and rownum<2; if(status_=23) then
select fee2 into fee_standard_id_ from t_user_a@dbfee15 where fee_user_tel = sq_co_sim_ and rownum<2;
elsif(status_ in (24,25)) then
select fee1 into fee_standard_id_ from t_user_a@dbfee15 where fee_user_tel = sq_co_sim_ and rownum<2;
end if; insert into t_user_zd@dbfee15(id,fee_user_tel,buss_type,add_taocan_time,fee_standard_add_time,fee_standard_person,fee_standard_id) values
(t_fee_users.nextval@dbfee15,From_Tel_,0,sysdate,sysdate,'admin',fee_standard_id_);
update t_user_reg@map15 set dw_stat='01' where reg_sim=From_Tel_;
-- commit;
temp4_:=1;
exception
when no_data_found then
begin
temp4_:=2;
end;
when others then
temp4_:=3;
end;
end if;
if((temp1_ =1 OR temp2_=1)and(temp1_!=2 and temp2_!=2)) then
Msgcontent := '对不起您的功能已经开通,无需再次开通，详情请咨询0371－65350110，本条信息免费';
dbms_output.put_line(Msgcontent);
goto proc_end;elsif (temp1_ =3 and temp2_ =3) then
Msgcontent := '对不起，您没有注册，不能使用此命令，详情请咨询0371－65350110，本条信息免费!';
dbms_output.put_line(Msgcontent);
goto proc_end;
end if ;
end;
if(temp3_=3 OR temp4_=3) then
dbms_output.put_line(temp3_);
Msgcontent := '对不起，您开通功能失败，详情请咨询0371－65350110，本条信息免费!';
dbms_output.put_line(Msgcontent);
goto proc_rollback;
elsif((temp3_=1 OR temp4_=1) AND (temp3_!=3 OR temp4_!=3)) then
Msgcontent := '您已成功开通功能!';
dbms_output.put_line(Msgcontent);
goto proc_end;
elsif(temp3_=2 AND temp4_=2) then
Msgcontent := '对不起，您未授权任何企业或单位，无需开通功能，详情请咨询0371－65350110，本条信息免费!';
dbms_output.put_line(Msgcontent);
goto proc_end;
end if;
<<proc_rollback>>
rollback;
<<proc_end>>
Insert Into Smsintf.Sms_Int_Send_All
(Db_Sequ, Sp_Number, Apply_Date, Charge_Tel, To_Tel, Server_Type, Fee, Feetype, Mt_Flag, Expire_Time,
Send_Times, Msg, Reserved)
Values
(Smsintf.Sms_Int_Send_Sequ.Nextval, '123456789', Sysdate, From_Tel_, From_Tel_, '123456', 0, 2, 0, Sysdate + 1, 1,
Msgcontent, Reserved_);
Update Smsintf.Sms_Int_Recv_All Set Deal_Flag = 2 Where Deal_Flag = 0 And Rowid = Rowid_;
Commit;
exception
when others then
rollback;
dbms_output.put_line('tishi1'+sqlerrm);
dbms_output.put_line('tishi2'+sqlcode);
End; end;
这个存储过程的调用过程是，服务器程序调用存储过程A，然后存储过程A再调用这个存储过程DW_NEWY。在这个存储过程中调用了四个数据库连接，已经把数据库连接改为了10，刚开始可以调用，可是调用之后，一般在24小时后，就会死掉，没有任何抱错，然后整个存储过程包括存储过程A都不再运行。请朋友们帮我看看是什么原因。create or replace PROCEDURE DW_NEWY_ALL -- 联通员工激活自己状态
(From_Tel_ IN VARCHAR2, Smsmsg_ IN VARCHAR2,Rowid_ IN VARCHAR2,Reserved_ IN VARCHAR2) AS
Begin
Declare
temp1_ number(5);
temp2_ number(5);
temp3_ number(5);
temp4_ number(5);
status_ number(20);
dw_stat_ varchar2(2);
feetype_ varchar2(2);
ceshi_ varchar2(2);
smsmsg varchar2(10);
zdfee_ varchar2(8);
fee_standard_id_ varchar2(30);
sq_co_sim_ varchar2(11);
Msgcontent varchar2(360);
Begin
begin
temp1_ := existsan(from_tel_);--判断在188数据库是否存在
temp2_ := existhn(from_tel_);--判断在15数据库是否存在if (temp1_ =2 ) then --在188上激活
begin
select status, dw_stat ,fee_type,ceshi into status_ ,dw_stat_,feetype_,ceshi_ from t_user_reg@SANHEYI188 where reg_sim=from_tel_;
select sq_co_sim into sq_co_sim_ from t_sq_user@SANHEYI188 where reg_sim=from_tel_ and rownum<2;
if(status_=23) then
select tc.id into fee_standard_id_ from t_user_a@FEE188 tu, t_tc_a@FEE188 tc where tu.fee_standard_id=tc.fee_name and tc.buss_type='T_QYGNJ' and tu.fee_user_tel=sq_co_sim_;
elsif(status_ = 24) then
select tc.id into fee_standard_id_ from t_user_a@FEE188 tu, t_tc_a@FEE188 tc where tu.fee_standard_id=tc.fee_name and tc.buss_type='U_QYGN' and tu.fee_user_tel=sq_co_sim_; elsif(status_ = 25) then
select tc.id into fee_standard_id_ from t_user_a@FEE188 tu, t_tc_a@FEE188 tc where tu.fee_standard_id=tc.fee_name and tc.buss_type='M_QYGN' and tu.fee_user_tel=sq_co_sim_; elsif(status_ = 22) then
select tc.id into fee_standard_id_ from t_user_a@FEE188 tu, t_tc_a@FEE188 tc where tu.fee_standard_id=tc.fee_name and tc.buss_type='J_QYGN' and tu.fee_user_tel=sq_co_sim_;
end if;
insert into t_user_zd@FEE188(id,fee_user_tel,buss_type,add_taocan_time,fee_standard_add_time,fee_standard_person,fee_standard_id) values
(t_fee_users.nextval@FEE188,from_tel_,0,sysdate,sysdate,'admin',fee_standard_id_);
update t_user_reg@SANHEYI188 set dw_stat='01' where reg_sim=from_tel_;
temp3_:=1;
exception
when no_data_found then
begin
temp3_:=2;
end;
when others then
temp3_:=3;
end;
end if;if ( temp2_= 2) then --在15上激活
begin
select status, dw_stat ,fee_type,ceshi into status_ ,dw_stat_,feetype_,ceshi_ from t_user_reg@map15 where reg_sim=From_Tel_;
select sq_co_sim into sq_co_sim_ from t_sq_user@map15 where reg_sim=From_Tel_ and rownum<2; if(status_=23) then
select fee2 into fee_standard_id_ from t_user_a@dbfee15 where fee_user_tel = sq_co_sim_ and rownum<2;
elsif(status_ in (24,25)) then
select fee1 into fee_standard_id_ from t_user_a@dbfee15 where fee_user_tel = sq_co_sim_ and rownum<2;
end if; insert into t_user_zd@dbfee15(id,fee_user_tel,buss_type,add_taocan_time,fee_standard_add_time,fee_standard_person,fee_standard_id) values
(t_fee_users.nextval@dbfee15,From_Tel_,0,sysdate,sysdate,'admin',fee_standard_id_);
update t_user_reg@map15 set dw_stat='01' where reg_sim=From_Tel_;
-- commit;
temp4_:=1;
exception
when no_data_found then
begin
temp4_:=2;
end;
when others then
temp4_:=3;
end;
end if;
if((temp1_ =1 OR temp2_=1)and(temp1_!=2 and temp2_!=2)) then
Msgcontent := '对不起您的功能已经开通,无需再次开通，详情请咨询0371－65350110，本条信息免费';
dbms_output.put_line(Msgcontent);
goto proc_end;elsif (temp1_ =3 and temp2_ =3) then
Msgcontent := '对不起，您没有注册，不能使用此命令，详情请咨询0371－65350110，本条信息免费!';
dbms_output.put_line(Msgcontent);
goto proc_end;
end if ;
end;
if(temp3_=3 OR temp4_=3) then
dbms_output.put_line(temp3_);
Msgcontent := '对不起，您开通功能失败，详情请咨询0371－65350110，本条信息免费!';
dbms_output.put_line(Msgcontent);
goto proc_rollback;
elsif((temp3_=1 OR temp4_=1) AND (temp3_!=3 OR temp4_!=3)) then
Msgcontent := '您已成功开通功能!';
dbms_output.put_line(Msgcontent);
goto proc_end;
elsif(temp3_=2 AND temp4_=2) then
Msgcontent := '对不起，您未授权任何企业或单位，无需开通功能，详情请咨询0371－65350110，本条信息免费!';
dbms_output.put_line(Msgcontent);
goto proc_end;
end if;
<<proc_rollback>>
rollback;
<<proc_end>>
Insert Into Smsintf.Sms_Int_Send_All
(Db_Sequ, Sp_Number, Apply_Date, Charge_Tel, To_Tel, Server_Type, Fee, Feetype, Mt_Flag, Expire_Time,
Send_Times, Msg, Reserved)
Values
(Smsintf.Sms_Int_Send_Sequ.Nextval, '123456789', Sysdate, From_Tel_, From_Tel_, '123456', 0, 2, 0, Sysdate + 1, 1,
Msgcontent, Reserved_);
Update Smsintf.Sms_Int_Recv_All Set Deal_Flag = 2 Where Deal_Flag = 0 And Rowid = Rowid_;
Commit;
exception
when others then
rollback;
dbms_output.put_line('tishi1'+sqlerrm);
dbms_output.put_line('tishi2'+sqlcode);
End; end;
看代码找不出问题，应该像利平兄说的，看alertSid.log里有没有错误信息，然后根据错误信息排查代码
ORA-02020: too many database links in use
Cause: The current session has exceeded the INIT.ORA open_links maximum.
Action: Increase the open_links limit, or free up some open links by committing or rolling back the transaction and canceling open cursors that reference remote databases.
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_links integer 10
open_links_per_instance integer 4这是我的数据库连接配置
ORA-01012: not logged on
ORA-02396: exceeded maximum idle time, please connect again
ORA-02063: 紧接着line（源于DBFEE15）
这是发现的错误，原因是在dbfee15中限制了3分钟自动清除的原因导致的。现在这个问题已经解决。
现在的新问题是有的用户可以激活，有的激活不了，出错的环节，我们认定在这一段代码中产生的错误，具体原因还在检查中。欢迎朋友们继续发言
insert into t_user_zd@dbfee15(id,fee_user_tel,buss_type,add_taocan_time,fee_standard_add_time,fee_standard_person,fee_standard_id) values
(t_fee_users.nextval@dbfee15,From_Tel_,0,sysdate,sysdate,'admin',fee_standard_id_);
update t_user_reg@map15 set dw_stat='01' where reg_sim=From_Tel_;