The DELETE command is used to remove the Tables data either selective or all and put the data in an rollback segment as well where from a read consistent view or a flashback view can be seen even after commit and one can rollback
the tables data after delete as well. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.one more thing delete doesn' resets the HWM and space occupied with insert can't be reclaim
by delete.when you deletes table data indexes are not dropped.Its a DML statement。We can use undo space。
hr@ORCL> select * from t;
ID TEST
---------- ----------
1 g
3 d
5 t
hr@ORCL> commit;
Commit complete.
hr@ORCL> delete from t where id=5;
1 row deleted.
hr@ORCL> select * from t;
ID TEST
---------- ----------
1 g
3 d
hr@ORCL> rollback;
Rollback complete.
hr@ORCL> select * from t;
ID TEST
---------- ----------
1 g
3 d
5 t
Truncate command removes all the data from table and drops all integrity constraints it's an DDL Statement and resets the HWM but you can't rollback a tables data after truncate, As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.We
can not use undo space and one more thing truncate doesn' remove selective rows it can only removes the full tables data.truncate leaves the database structure ad is a good tool to reset HWM.but a dangerous tool for database security.
hr@ORCL> select * from t;
ID TEST
---------- ----------
1 g
3 d
5 t
hr@ORCL> truncate table t;
Table truncated.
hr@ORCL> select * from t;
no rows selected
hr@ORCL> rollback;
Rollback complete.
hr@ORCL> select * from t;
no rows selected
The drop command means delete the table from the database. The drop command is a data definition language (DDL) command and this is a table oriented command. We can delete all rows and also the table definition, indexes, privileges,triggers and all storage
parameters. The rollback is not possible. We can not use undo space。
hr@ORCL> drop table t;
Table dropped.
hr@ORCL> select * from t;
select * from t
*
ERROR at line 1:
ORA-00942: table or view does not exist
hr@ORCL> rollback;
Rollback complete.
hr@ORCL> select * from t;
select * from t
*
ERROR at line 1:
ORA-00942: table or view does not exist
In sum,DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.From Oracle 10g a table can be "undropped". Example:
hr@ORCL> flashback table t to before drop;
Flashback complete.
分享到:
相关推荐
sql之truncate_、delete与drop区别.pdf sql之truncate_、delete与drop区别.pdf
truncate,delete以及drop区别汇总,需要可以自己下载看看,个人觉得蛮好的!
sql之truncate、delete与drop区别
详细阐述了Oracle中三种删除的方式truncate,drop和delete三者的区别和联系.
truncate,delete,drop的异同点
作为初学者,有时容易混淆truncate,delete,drop之间的根本区别,所有今日小记一下。。。
delete,truncate和drop的区别
TRUNCATE与 DELETE区别,比较两个的不同的特点
您可能感兴趣的文章:数据库中删除语句Drop、Delete、Truncate的相同点和不同点的比较(实例说明)drop,truncate与delete的区别详解MySQL中DROP,TRUNCATE 和DELETE的区别实现mysql从零开始浅析drop user与delete from ...
主要介绍了秒懂drop、truncate和delete的区别,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下
sql中 truncate 和 delete 有什么区别
本文主要讲mysql中三种删除表的操作,delete语句、truncate语句以及drop语句的区别: 简介 delete 1、删除整张表的数据: delete from table_name; 2、删除部分数据,添加where子句: delete from table_name ...
前言 上周同事小姐姐问我:“哈哥你看,我发现...咱们常用的三种删除方式:通过 delete、truncate、drop 关键字进行删除;这三种都可以用来删除数据,但场景不同。 一、从执行速度上来说 drop > truncate >> DELETE 二
注意:这里说的delete是指不带where子句的delete语句 相同点 truncate和不带where子句的delete, 以及drop都会删除表内的数据 不同点: 1. truncate和 delete只删除数据不删除表的结构(定义) drop语句将删除表的结构被...
SQL语句中----删除表数据drop、truncate和delete的用法,对你爱不完
drop、delete、truncate都表示删除,但是三者有一些差别: Delete用来删除表的全部或者一部分数据行,执行delete之后,用户需要提交(commmit)或者回滚(rollback)来执行删除或者撤销删除。会触发这个表上所有的...
9.1.3 drop,delete与truncate的区别
test 博文链接:https://fly533.iteye.com/blog/792601