Results 1 to 6 of 6

Thread: Truncate/Detete

  1. #1
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    Truncate/Detete

    While Truncating a table i am getting an error resource is busy. I felt that some user is accesing the table. But at the same movement i deleted full data in a table using delele and i commited.Table is deleted successfully.There is no error message of resource is busy.Why...............


  2. #2
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: Truncate/Detete

    beause Delete is DML it deletes only the data.
    Truncate is DDL it changes the table structure it self, which is not allowed while another user is connected to the table.


  3. #3
    Contributing Member
    Join Date
    Apr 2006
    Answers
    46

    Re: Truncate/Detete

    moderator said you the perfect ans i want to add somthing to it for knowledge sharing------

    truncate statement resets the hwm(high water mark) to it's initial value and free the space.


  4. #4
    Junior Member
    Join Date
    Oct 2007
    Answers
    12

    Re: Truncate/Detete

    In addition to above answers, when you fire DELETE the data will move to rollback segment, due to which it is slower than Truncate but you can recover the data.


  5. #5

    Re: Truncate/Detete

    Truncate is a DDL command where the auto commit is enabled and the rollback is not possible, so the error message is generated when another user is accessing the same table.

    But Delete is a DML command where the rollback is possible, so even the another user is using also u can use the command and make it sure by giving commit.

    Last edited by sridharrganesan; 11-02-2007 at 05:24 AM.

  6. #6
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Thumbs up Re: Truncate/Detete

    Quote Originally Posted by sridharrganesan View Post
    Truncate is a DDL comment where the auto commit is enabled and the rollback is not possible, so the error message is generated when another user is accessing the same table.

    But Delete is a DML comment where the rollback is possible, so even the another user is using also u can use the comment and make it sure by giving commit.
    Truncate and Delete are not comments they are COMMANDS .
    DDL and DML respectively .


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact