CREATE OR REPLACE PROCEDURE PRO_ZMES_PP_CLOSE_DEL
IS
AUF VARCHAR2(12);
BEGIN
LOOP
SELECT AUFNR INTO AUF FROM ZMES_PP_CLOSE_DEL WHERE ROWNUM<=1;
IF SQL%FOUND THEN
DELETE FROM ZMES_PP_WOCONFM WHERE MANDT='800' AND AUFNR=AUF AND PROCESS_FLG='2';
DELETE FROM ZMES_PP_CLOSE WHERE MANDT='800' AND AUFNR=AUF AND PROCESS_FLG='2';
DELETE FROM ZMES_PP_CLOSE_DEL WHERE AUFNR=AUF;
COMMIT;
END IF;
END LOOP;
EXCEPTION
WHENOTHERS THEN
ROLLBACK;
END PRO_ZMES_PP_CLOSE_DEL;
/
SID EVENT
WAIT_TIMESTATE
15 enqueue
0
WAITING
30db file sequential read -1
WAITED KNOWN TIME
其他的等待事件是smon timer ，rdbms ipc message，SQL*Net message to client，SQL*Net more data to client，SQL*Net message from client，应该跟我这个会话关系不大吧
最初由 hanson 发布
[B]运行PRO_ZMES_PP_CLOSE_DEL 的sid是30吗？这表示在进行索引扫描。
你可以根据对应的p2找到到底是哪个表。
select segment_name from dba_segments where p2的值 between block_id and block_id+blocks [/B]
between block_id and block_id+blocks 我到那里找block_id和blocks的值
CREATE OR REPLACE PROCEDURE PRO_ZMES_PP_CLOSE_DEL
IS
AUF VARCHAR2(12);
BEGIN
LOOP
SELECT AUFNR INTO AUF FROM ZMES_PP_CLOSE_DEL WHERE ROWNUM<=1;
IF SQL%FOUND THEN
DELETE FROM ZMES_PP_WOCONFM WHERE MANDT='800' AND AUFNR=AUF AND PROCESS_FLG='2';
DELETE FROM ZMES_PP_CLOSE WHERE MANDT='800' AND AUFNR=AUF AND PROCESS_FLG='2';
DELETE FROM ZMES_PP_CLOSE_DEL WHERE AUFNR=AUF;
COMMIT;
END IF;
END LOOP;
EXCEPTION
WHENOTHERS THEN
ROLLBACK;
END PRO_ZMES_PP_CLOSE_DEL;
/ [/B]
怀疑是DELETE时候报错，然后跳到异常处理部分自动终止了。
你可以在异常处理中把错误信息写到一张日志表中。
btw:这种循环删除数据的做法效率极低。
