Hi list I have a session waiting forever on a "db file sequential read" wait event during a mass delete. The statement looks like this: DELETE FROM tcdhispersversnr a WHERE EXISTS (SELECT idvistamaster FROM tcdhispersversnr b WHERE b.pkfk = a.pkfk AND b.idvistamaster = a.idvistamaster AND a.idvistadetail IS NULL AND b.idvistadetail IS NOT NULL AND ( (CAST(A.ZHI_SYSEINDAT AS TIMESTAMP(0)) >= CAST(B.ZHI_SYSEINDAT AS TIMESTAMP(0)) AND CAST(A.ZHI_SYSEINDAT AS TIMESTAMP(0)) < CAST(B.ZHI_SYSERSDAT AS TIMESTAMP(0))) OR (CAST(B.ZHI_SYSEINDAT AS TIMESTAMP(0)) >= CAST(A.ZHI_SYSEINDAT AS TIMESTAMP(0)) AND CAST(B.ZHI_SYSEINDAT AS TIMESTAMP(0)) < CAST(A.ZHI_SYSERSDAT AS TIMESTAMP(0))) ) AND ( (A.ZHI_SYSGUADAT >= B.ZHI_SYSGUADAT AND A.ZHI_SYSGUADAT < B.ZHI_SYSGUBDAT) OR (B.ZHI_SYSGUADAT >= A.ZHI_SYSGUADAT AND B.ZHI_SYSGUADAT < A.ZHI_SYSGUBDAT) ) ) The block the session is waiting for belongs to an index on table tcdhispersversnr. I was thinking it might be a problem that the index is updated by the delete part of the statement while the sub-select part of the statement uses the index. Both the table in the delete-part and the table being queried in the sub-select are the same. NOTHING else is happening on this machine. No locks or latches or memory or undo problems whatsoever. Anybody any idea what might cause that problem? At totally frustrated yours, Stefan ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------