CREATE OR REPLACE PROCEDURE UPDATEALLSTOP
(
cityname IN NVARCHAR2
)
AS
BEGIN
delete from PT_STOP where PTS_CITY=cityname;
FOR REC IN ( SELECT * FROM PT_LINE where PTL_CITY=cityname )
LOOP
inputstop(cityname, rec.PTL_NAME, rec.PTL_GOSTOPLIST, rec.PTL_RTNSTOPLIST);
END LOOP;
COMMIT;
END;
上面过程,每次执行到delete from PT_STOP where PTS_CITY=cityname;时就过不去,为什么?不是权限的问题,用SYS用户也不行。
(
cityname IN NVARCHAR2
)
AS
BEGIN
delete from PT_STOP where PTS_CITY=cityname;
FOR REC IN ( SELECT * FROM PT_LINE where PTL_CITY=cityname )
LOOP
inputstop(cityname, rec.PTL_NAME, rec.PTL_GOSTOPLIST, rec.PTL_RTNSTOPLIST);
END LOOP;
COMMIT;
END;
上面过程,每次执行到delete from PT_STOP where PTS_CITY=cityname;时就过不去,为什么?不是权限的问题,用SYS用户也不行。
到oem中看看异常情况。
delete from PT_STOP where PTS_CITY=cityname; 的记录是否非常多,如果非常多的话,很有可能就是数据库的日志文件不够了
执行到
delete from PT_STOP where PTS_CITY=cityname;
就停止,也不报错。
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time; 如果有长期出现的一列,可能是没有释放的锁。
然后可以用下面SQL语句杀掉长期没有释放非正常的锁: alter system kill session 'sid,serial#';
那这个过程该怎么写?是否要每次判断一下是否锁定,然后执行delete操作吗?