REMOVING ROWS FROM A TABLE

We can remove rows from a table using different ways. First of them is using DELETE command. It will remove row from a table. Using WHERE clause we specify which rows to be deleted. If we omit WHERE clause all rows from the table will be deleted.

It is important to remember that DELETE command is a DML command and it can be rolled back. It means that if we want to remove rows permanently we need to COMMIT.

Examples:



The DELETE command is not COMMITED yet, so we are able to undo it:



If we want to remove rows permanently, we should COMMIT



We can see that after DELETE command is committed, rollback won't be able to undo the changes.

TRUNCATE TABLE

This command will remove ALL ROWS from a table. It is important to remember that it can not be rolled back.

Example:



If we want to remove whole table from a database ( rows, privileges, indexes), we need to use DROP command.



Rollback will again not undo changes made with DROP command.



Screen clipping taken: 4.2.2011 г.; 11:27 ч.

There is way in Oracle 10g to undo "dropping table" using FLASHBACK.