oracle中delete drop truncate的用法和区别

      数据库的运维中，经常会遇到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

例如：truncate table 后，有可能表空间仍没有释放，可以使用如下语句：

alter table 表名称 deallocate   UNUSED KEEP 0;

注意如果不加KEEP 0的话，表空间是不会释放的。

或者：

TRUNCATE TABLE (schema)table_name DROP(REUSE) STORAGE才能释放表空间。

例如： truncate table  test1 DROP STORAGE;

三、drop

注：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 可以。

总结：

补充要注意的：

还要注意alter table move过程中会产生锁，应该避免在业务高峰期操作！

2、补充一些PURGE知识

6). Purge index recycle_bin_object_name： 当想释放Recycle bin的空间，又想能恢复表时，可以通过释放该对象的index所占用的空间来缓解空间压力。 因为索引是可以重建的。

亿速云「云服务器」，即开即用、新一代英特尔至强铂金CPU、三副本存储NVMe SSD云盘，价格低至29元/月。点击查看>>