温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

oracle中delete drop truncate的用法和区别

发布时间:2020-07-02 13:05:37 阅读:3358 作者:xjsunjie 栏目:关系型数据库
开发者测试专用服务器限时活动,0元免费领,库存有限,领完即止! 点击查看>>

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

总结:

补充要注意的:

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

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

向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI

开发者交流群×