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...............
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.
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.
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.
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.
Re: Truncate/Detete
Truncate and Delete are not comments they are COMMANDS .Originally Posted by sridharrganesan
DDL and DML respectively .
Posting Permissions
Reply With Quote