commit, rollback and truncate commands in sql*plus?

Question

Can somebody please explain clearly the relation between commit , rollback and truncate commands in Oracle 10G sql*plus.Thanks

Answer 1

COMMIT command - Once you fire a commit command the changes made by you in the database are saved and you cannot undo these changes.

syntax: COMMIT



ROLLBACK command - Rollback command is just like a restore point on your computer. If this command fired just by writing ROLLBACK then it will revert the changes made by you in the database untill last COMMIT mark. But ROLLBACK can also be used to undo changes upto certain points by using a clause TO SAVEPOINT. But if the data is committed the SAVEPOINT cannot also help you.



syntax: ROLLBACK [WORK] [TO [SAVEPOINT] ];

syntax: SAVEPOINT ;





TRUNCATE command - This command deletes all your rows of the table and makes it empty. Although it is not entirely true, the difference between emptying table with TRUNCATE and DELETE is that DELETE command deletes the rows one by one and TRUNCATE command removes entire table and re-create a new one with the same name. So TRUNCATE is faster than DELETE in case of emptying the table.

syntax: TRUNCATE TABLE ;



Also DDL commands are auto-commited hence once you fired any CREATE, ALTER, TRUNCATE, DROP command then you cannot revert the changes. Notice that TRUNCATE is also a DDL command but DELETE is a DML command.

Answer 2

Sql Rollback Command

Answer 3

Simple:

Commit : This command ensures that the state of the data is written in the database.You cannot rollback once commited.

Rollback : This command rolls back any changes made to a databsed after the last commit.So if you insert any rows and do not write a commit they can be rolled backed without writing any delete commands.

Truncate: Its to delete the content of a table and commit at the same time.You cannot rollback after a truncate command

Answer 4

Have a look at http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10006.htm for info on truncate. I'm assuming you mean how commit and rollback work ( or not ) with truncate.

*Edit*

Forgot one of Oracles more interesting features - Flashback. Allows you to rollback the database to a point in time regardless of whats happened since. Bit like restoring the database without having to restore it.

The main thing about truncating a table is that it will release the storage space used by the data, if you simply delete rows the table still occupies the same amount of disk space as before.