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


本文出自 “滴水穿石孙杰” 博客,请务必保留此出处http://xjsunjie.blog.51cto.com/999372/1847337

本页内容版权归属为原作者,如有侵犯您的权益,请通知我们删除。
最近有一个服务器升级的项目,得知用户需求是03服务器上的文件、DNS、DHCP要迁移到Server 2012上,文件服务器最重要的当然是保留权限的设置,DHCP和DNS服务器实现迁移相对来说很简单 于是我便模拟了以下环境先进行测试,项目完成之后再做总结 = = 环境介绍 DC 192.168.124.250 Migration 192.168.124.251(Server 2012 R2 Datacenter,将03的服务器迁移到此服务器) FDDS 192.168.124.252(Server 2003
CentOS6中关于网络配置的命令有很多,本文将介绍几个平时最长用的几个命令,以及网卡IP地址的配置和简单路由配置。 1、经常使用的查看IP地址命令为 ifconfig,不跟参数的情况下默认查看所有已启用的网卡信息,如下图所示: 如果想查看具体某块网卡信息,则可以在ifconfig后面跟上网卡设备,如只查看eth0的信息则执行:ifconfig eht0 即可。 禁用某块网卡:结合down命令可以禁用某块网卡,如要禁用eth0网卡,则执行:ifconfig eth0 down 即可,这种禁用只是临时性的,
为Windows Server 2012 R2指定授权服务器 在Windows Server 2008 R2的终端服务中,可以手动指定授权服务器,而在Windows Server 2012 R2中,默认只能通过"远程桌面连接服务"管理器,指定授权服务器,而要使用远程桌面连接服务管理器,则需要安装一系列的组件,但大多数的时候,我们只是想配置一台"远程桌面会话主机",不想要安装"远程桌面网关服务"、"远程桌面Web代理"这些组件,那么,有没有办法和Windows Server 2008 R2一样,为Windo
移除VMware View桌面中孤立的主机与桌面池 在使用VMware View桌面的过程中,如果由于多种因为(例如重新安装了vCenter Server)导致View桌面池丢失,想要在View Administrator中删除这些孤立的虚拟机与桌面池,可以使用如下的方法。 图1-1 停留在"正在删除" 图1-2 停止在"正在删除" 1 登录View Composer删除孤立虚拟机 进入View Composer的服务器,打开View Composer安装位置,复制该路径,如图1-3所示。默认情况下,此路
今天发现有一台线上的服务器内存报警,最近报警发现有好几次了,慎是恼火,想一探究竟,看了一下是一台16G内存的服务器,free 了一下看了一下确实是没有多少内存可用了,再看了一下都跑了一些什么应用,结果发现只跑了一个数据库跟一个跨服,当时心里就感觉16G内存不可能用完, [root@hqg-222-99.logs]#psaux|awk'{sum=sum+$6};END{printsum/1024"M"}'4363.01M才使用了4g多一点#也可以通过这段在网上找到的脚本查看#/bin/bashforPROC
之前在上linux课的时候,老师说,vim使用的好的网络管理员比普通的效率快好多。 合抱之木,生于毫末;九层之台,起于累土;千里之行,始于足下。撸基础吧。 linux编辑工具 VI VIM EMACS vim 是vi的升级版本,它不仅兼容vi的所有指令,而且还有一些新的特性在里面。vim的这些优势主要体现在以下几个方面: 易用性 vi只能运行于unix中,而vim不仅可以运行于unix,windows ,mac等多操作平台。 语法加亮 vim可以用不同的颜色来加亮你的代码。 可视化操作( ESC+V) 就
CentOS 6.8 GRUB加密和破解密码实战指南 案例1:服务器在公共场合,为了防止随便有人进入单用户破解root密码,先对GRUB引导进行加密,为了更加安全对启动内核时也加密 1、编辑grub配置文件 [root@localhost~]#opensslpasswd-1//MD5加密转换Password:Verifying-Password:$1$X8cVMw5v$AH0aUHVNix7Tx6wmHAXsf1[root@localhost~]#vim/etc/grub.conf#grub.confge
故障: 在HP 785G6(服务器有2个集成网卡,4个独立网卡)上装centos6.5后配置网卡信息后,重启网卡报错,提示Bringing uo interface eth0:bnx2 0000:02:03.0:etho:register_cnicfailed Determining if ip address isalready in use for device eth0,网络不通,截图如下 排查经过 :查看配置文件, [root@HGJ0205 ~]# cat /etc/sysconfig/netw
之前的话我们的项目都是跑在windows上,今天我们要将我们的程序跑到linxu机器上。在看linux部署之前,我们先看一下node.js类似于asp.net mvc的过滤器或者叫拦截器。在app.js中我们加入如下代码 varbeforeRequest=function(req,res,next){if(req.originalUrl=='/'||req.originalUrl=='/login'||req.originalUrl=='/config'||req.originalUrl=='/user'

数据库备份mysqldump应用总结 - 2016-09-12 14:09:16

[root@ansible~]#mysqldump-uroot-p123456xxx/opt/xxx.sql#备份数据库xxx[root@ansible~]#egrep-v"#|\*|--|^$"/opt/xxx.sqlDROPTABLEIFEXISTS`test`;CREATETABLE`test`(`id`int(4)NOTNULLAUTO_INCREMENT,`name`char(20)NOTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=7DEF