Problems deleting the table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Problems deleting the table

  1. #1
    Join Date
    Mar 2003
    Posts
    32

    Question Problems deleting the table

    Hi
    I have a table name comments.
    In this table there is a column of varchar2(1500).
    When i try to delete the records in the table my system just gets hangs. Can anybody just tell what should i do to delete record from the table.
    By the way it doesnot have lots of rows, only 10 rows.
    Im using Oracle 9i version 9.1.

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    If u dont want data at all, then Truncate table...

    it will be much much faster...i can tell few secs..

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    This may be a deadlock situation. When a DML transaction is not complete on the table by another session/user, it occurs.

    Make sure that u are the only person working on the table.

    Check this out...
    http://technet.oracle.com/docs/produ...cnsis.htm#6037

    HTH.
    Last edited by ggnanaraj; 03-18-2003 at 07:48 AM.

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by ggnanaraj
    This may be a deadlock situation. When a DML transaction is not complete on the table by another session/user, it occurs.

    Make sure that u are the only person working on the table.

    HTH.
    BTW its not called DEADLOCK situation...this sessions TRAN will jus be waiting on that TRAN to complete...can say this sessionis blocked by that....

    Typical Deadlock situation goes something like this

    TRAN1 needs to update / delete on 2 tables and does 1...say TableA..

    TRAN2 also needs to update / delete on same 2 tables and does 1 say Table2....

    Now Tran2 would like to lock tableA but is being locked by TRAN1 and inturn Tran1 wants to lock TableB but cannot do so as Tran2 has locked that table....
    So both TRANs will be waiting for one another to complete which will never happen....and the situation is Deadlock..


    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #5
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    The following simulates your environment:

    SEssion 1:
    insert into temp values('1');
    commit;

    Session 2:
    insert into temp values('2');

    SEssion 1:
    delete from temp;
    1 row deleted.

    Session 2:
    delete from temp;
    (hangs)

    SEssion 1:
    commit;

    Session 2:
    (comes alive)

    HTH.

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    10 rows is realy nothing at all -- a straight delete ought to perform for you. Maybe you have a deadl;ock, but probably not I would say as Oracle will kill off one of the sessions it a deadlock is detected.

    How long does it hang for?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    Originally posted by ggnanaraj
    The following simulates your environment:

    SEssion 1:
    insert into temp values('1');
    commit;

    Session 2:
    insert into temp values('2');

    SEssion 1:
    delete from temp;
    1 row deleted.

    Session 2:
    delete from temp;
    (hangs)

    SEssion 1:
    commit;

    Session 2:
    (comes alive)

    HTH.
    If I dont give a commit at the end of the above quote for session 1. It waits/hangs indefinitely in Session 2. So this is not a deadlock (which is detected by the database) but a lock.

    HTH.

  8. #8
    Join Date
    Aug 2002
    Posts
    115
    ya. I think the above example illustrates a LOCK and not a DEADLOCK.

    I think a deadlock would be encountered when session A is waiting for session B to complete something while session B is waiting for session A to complete someting else.

    Try killing all sessions except that of oracle
    SELECT sid, serial# FROM v$session WHERE username IS NOT NULL

    and ALTER SYSTEM KILL SESSION 'SID,SERIAL#';

    and then retry truncating the table.

Posting Permissions

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


Click Here to Expand Forum to Full Width