Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Posts
    42

    Unanswered: Regarding Delete

    Hi,
    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.

    Thanks in advance.

    Regards
    Rasmi

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    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 ???

    Gregg

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    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 H.TYPE=W.TYPE
    AND H.ID1=W.ID1
    AND H.ID2=W.ID2
    AND B.SID=H.SID
    AND W.SID=A.SID
    AND (O.SID=W.SID OR O.SID=H.SID)
    AND O.OWNER <> 'SYS'
    ORDER BY 1;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •