oracle中delete drop truncate的用法和区别

原文链接：http://xjsunjie.blog.51cto.com/999372/1847337

如需转载请联系听云College团队成员小尹邮箱：yinhy#tingyun.com

数据库的运维中，经常会遇到delete drop truncate的操作，那么如何去把握它们的用法和区别呢？

比如当数据库空间爆满，已经增长到存储空间单个存储文件的最大值32G。你需要通过一些办法释放掉表空间或者扩容表空间来解决问题。

一般当系统中大量使用分区表，而针对分区表清除数据，是不会释放表空间的，必须把分区drop掉，才会释放空间。

下面我们具体了解一下这三个命令：

一、delete

1、delete是DML，执行delete操作时，每次从表中删除一行，并且同时将该行的的删除操作记录在redo和undo表空间中以便进行回滚（rollback）和重做操作，但要注意表空间要足够大，需要手动提交（commit）操作才能生效，可以通过rollback撤消操作。
2、delete可根据条件删除表中满足条件的数据，如果不指定where子句，那么删除表中所有记录。
3、delete语句不影响表所占用的extent，高水线(high watermark)保持原位置不变。
注：delete的可闪回恢复。

二、truncate

1、truncate是DDL，会隐式提交，所以不能回滚，不会触发触发器。truncate操作同没有where条件的delete操作十分相似，只是把表里的信息全部删除，但是表依然存在。
2、truncate会删除表中所有记录，并且将重新设置高水线和所有的索引，缺省情况下将空间释放到minextents个extent，除非使用reuse storage。不会记录日志，所以执行速度很快，但不能通过rollback撤消操作（如果一不小心把一个表truncate掉，也是可以恢复的，只是不能通过rollback来恢复）。
3、对于外键（foreignkey ）约束引用的表，不能使用truncate table，而应使用不带where子句的 delete 语句。
4、truncatetable不能用于参与了索引视图的表。
例如：truncate table 后，有可能表空间仍没有释放，可以使用如下语句：
alter table 表名称 deallocate   UNUSED KEEP 0;
注意如果不加KEEP 0的话，表空间是不会释放的。
或者：
TRUNCATE TABLE (schema)table_name DROP(REUSE) STORAGE才能释放表空间。
例如： truncate table  test1 DROP STORAGE;

三、drop

1、drop是DDL，会隐式提交，所以不能回滚，不会触发触发器。
2、drop语句删除表结构及所有数据，并将表所占用的空间全部释放。
3、drop语句将删除表的结构所依赖的约束，触发器，索引，依赖于该表的存储过程/函数将保留,但是变为invalid状态。
注：drop后的表被放在回收站(user_recyclebin)里，而不是直接删除掉。这样，回收站里的表信息就可以被恢复，或彻底清除。 通过查询回收站user_recyclebin获取被删除的表信息，然后使用语句
                   flashback table <user_recyclebin.object_name or user_recyclebin.original_name> to before drop [rename to <new_table_name>];
                   将回收站里的表恢复为原名称或指定新名称，表中数据不会丢失。
                   若要彻底删除表，则使用语句：drop table <table_name> purge;

Oracle命令delete truncate drop 的区别

1. delete/truncate 只删除数据不删除表，索引的结构。 drop 将删除表的结构及依赖的 index/constrain/trigger，依赖于该表的procedure/function 将保留，但是变为 invalid 状态；
2. delete 是 dml，写rollback segement，可回滚，速度慢，事务提交之后才生效。可使用 flashback闪回恢复。一次性大批量数据的 delete 可能导致回滚段急剧扩展从而影响到数据库，慎用触发 trigger。 truncate/drop 是 ddl，隐式提交，不写 rollback segment，不能回滚，速度快。
3. delete 不影响表所占用的 extent，HWM 保持原位置不动，即使删除的是最靠近 HWM 的数据。delete 其实也可以释放空间，但是不降低 HWM，delete 后 block 的空闲空间达到 pct_used，就可以重用。 truncate 缺省情况下将空间（表和索引）释放到 minextents 个 extent，除非使用 reuse storage。truncate 会将高水线复位（回到最开始）。 drop 将表所占用的空间全部释放，segment 不存在，无所谓 HWM 的概念；
Oracle高水位(HWM) 解释
http://blog.csdn.net/tianlesoftware/archive/2009/10/22/4707900.aspx        
4. truncate/drop 的对象必须是本模式下的，或者被授予 drop any table 的权限，但 drop any table 权限不能 truncate/drop sys 的表。 delete 的对象必须是本模式下的，或者被授予 delete on SCHEMA.table 或 delete any table 的权限，但 delete any table 权限不能 delete sys 的表；
5. 不能 truncate 一个带有 enable 外键的表，不管表里有没有数据，如果要 truncate，首先要 disable 外键或者删除外键（drop 外键的表肯定是删除了外键）。不能 drop 一个带有 enable 外键的表，不管表里有没有数据，如果要 drop，首先要删除外键，或者直接用 drop table TABLE_NAMEcascade constraints; 级联删除外键。 delete 可以。

总结：

1、在速度上，一般来说，drop> truncate > delete。
2、在使用drop和truncate时一定要注意，虽然可以恢复，但为了减少麻烦，还是要慎重。
3、如果想删除部分数据用delete，注意带上where子句，回滚段要足够大；如果想删除表，当然用drop；如果想保留表而将所有数据删除，如果和事务无关，用truncate即可；如果和事务有关，或者想触发trigger，还是用delete；如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。

补充要注意的：

1、alter table 表名 move 是通过消除行迁移，清除空间碎片，删除空闲空间，实现缩小所占的空间，但会导致此表上的索引无效（因为ROWID变了，无法找到），所以执行 move 就需要重建索引。
找到表对应的索引。
select index_name,table_name,tablespace_name,index_type,status  from dba_indexes  where table_owner='SCOTT' ;
根据status 的值，重建无效的就行了。
sql='alter index '||index_name||' rebuild'; 使用存储过程执行，稍微安慰。
还要注意alter table move过程中会产生锁，应该避免在业务高峰期操作！

2、补充一些PURGE知识

Purge操作：

1). Purge tablespace tablespace_name : 用于清空表空间的Recycle Bin
2). Purge tablespace tablespace_name user user_name: 清空指定表空间的Recycle Bin中指定用户的对象
3). Purge recyclebin: 删除当前用户的Recycle Bin中的对象
4). Purge dba_recyclebin: 删除所有用户的Recycle Bin中的对象，该命令要sysdba权限
5). Drop table table_name purge:  删除对象并且不放在Recycle Bin中，即永久的删除，不能用Flashback恢复。
6). Purge index recycle_bin_object_name： 当想释放Recycle bin的空间，又想能恢复表时，可以通过释放该对象的index所占用的空间来缓解空间压力。 因为索引是可以重建的。