专栏首页云技术+云运维oracle中delete drop truncate的用法和区别

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

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所占用的空间来缓解空间压力。 因为索引是可以重建的。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • vmware收集日志相关

     VMware 中收集诊断信息,可能包含来自 ESXi 主机和 vCenter Server 的支持包。在主机支持包中收集的数据可能被视为敏感数据。另外,从 v...

    孙杰
  • linux代理设置

    有些局域网环境上网需要使用代理上网,图形界面的很好解决就设置一下浏览器的代理就好了,但是Linux纯命令行的界面就需要手动配置了。

    孙杰
  • rhel7.2 yum使用CentOS 的更新包

    redhat 的更新包只对注册的用户生效,所以我们自己手动更改成CentOS 的更新包,CentOS几乎和redhat是一样的,所以无需担心软件包是否可安装,安...

    孙杰
  • 24. Python 正则

    (?P<name>…)省略的部分就是正常的正则匹配,我们把匹配到的这个整体给赋一个变量名字name,在后续调用的时候,就方便多了

    py3study
  • Python的-m参数

    1、在命令行中启动Python脚本的时候,经常会用到-m参数,那么-m起了什么作用呢?

    py3study
  • usb.core NoBackendError, pyinstaller

    在调试pyusb时,发现直接python scripts.py可以正常运行,但是打包成exe, 即经过了 pyinstaller -F scripts.py ...

    羽翰尘
  • 区块链现在到底有哪些实际的应用场景?

    如果说比特币的诞生是从零到一,那么区块链则是从一到N。与传统的技术相比,区块链代表着以P2P为基础的去中心化的新体系:从数字货币到智能合约、数字资产、存在性证明...

    广州闪链科技
  • Elasticsearch自定义分析器订单号搜索

    使用edge ngram将每个单词都进行进一步的分词和切分,用切分后的ngram来实现前缀搜索,比如’OD5046240000014238’这样一个订单号会被分...

    XING辋
  • Jquery实现的一种常用高亮效果

    Code <html> <head> <title>jquery</title> <style> body {...

    菩提树下的杨过
  • 破解Intellij Idea 原

        对不起了,Jetbrains,Intellij Idea很好用,但是太贵了,只好破解使用!

    克虏伯

扫码关注云+社区

领取腾讯云代金券