I have a query like
DELETE FROM <<TableName>> WHERE <<Column Name>> < "5567" which tries to delete 5000 rows. But the time when i am firing this statement the session is being haulted. I tried it first and it took around 30 minutes without successs. Even if it is not possible to delete 10 rows at one time. So i had to delete it one by one.What is the main cause behind it??Can anybody help me out.
What are the error messages you are getting ?? Do you have cascading deletes that are stopping you ??? What version of Oracle ??? If there a trace file in the udump directory that may lend a clue ?? Is there anything in the alertlog that may be helpful ???
How big is the table? If you run the following select, how fast does it run.
select count(*) FROM <<TableName>> WHERE <<Column Name>> < "5567";
Do you have an index on <<Column Name>>?
Is the table busy, if you are trying to delete a record which has a lock on it, you will wait. You can check for that by checking for blocking locks using the following sql
SELECT /*+ RULE */ H.SID "Holding SID",
W.SID "Waiting SID",
B.USERNAME "Holding User",
A.USERNAME "Waiting User",
TO_CHAR(B.SERIAL#, 'FM999999999999') "Holding Serial #" ,
TO_CHAR(A.SERIAL#, 'FM999999999999') "Waiting Serial #",
DECODE(W.TYPE, 'MR', 'Media Recovery', 'RT', 'Redo Thread','UN', 'User Name','TX', 'Transaction','TM', 'DML','UL', 'PL/SQL User Lock','DX', 'Distributed Xaction','CF', 'Control File','IS', 'Instance State','FS', 'File Set','IR', 'Instance Recovery','ST', 'Disk Space Transaction','TS', 'Temp Segment','IV', 'Library Cache Invalidation','LS', 'Log Start or Switch','RW', 'Row Wait','SQ', 'Sequence Number','TE', 'Extend Table','TT', 'Temp Table', W.TYPE) "Lock Type",
DECODE(H.LMODE, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(H.LMODE)) "Lock Mode", DECODE(W.REQUEST,0, 'None', 1, 'Null',2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(W.REQUEST)) "Lock Request Type",
O.OWNER||'.'||O.OBJECT "Object Name",
O.TYPE "Object Type",
W.ID1 "Lock ID 1",
W.ID2 "Lock ID 2"
FROM V$SESSION B,V$ACCESS O,V$LOCK H,V$LOCK W,V$SESSION A
WHERE H.LMODE <> 0
AND H.LMODE <> 1
AND W.REQUEST <> 0
AND (O.SID=W.SID OR O.SID=H.SID)
AND O.OWNER <> 'SYS'
ORDER BY 1;
You do not need a parachute to skydive. You only need a parachute to skydive twice.