oracle数据库表的删除delete drop truncate
数据库的运维中,经常会遇到delete drop truncate的操作,那么如何去把握它们的用法和区别呢?
一般当系统中大量使用分区表,而针对分区表清除数据,是不会释放表空间的,必须把分区drop掉,才会释放空间。
一、delete
1、delete是DML,执行delete操作时,每次从表中删除一行,并且同时将该行的的删除操作记录在redo和undo表空间中以便进行回滚(rollback)和重做操作,但要注意表空间要足够大,需要手动提交(commit)操作才能生效,可以通过rollback撤消操作。
2、delete可根据条件删除表中满足条件的数据,如果不指定where子句,那么删除表中所有记录。
二、truncate
1、truncate是DDL,会隐式提交,所以不能回滚,不会触发触发器。truncate操作同没有where条件的delete操作十分相似,只是把表里的信息全部删除,但是表依然存在。
2、truncate会删除表中所有记录,并且将重新设置高水线和所有的索引,缺省情况下将空间释放到minextents个extent,除非使用reuse storage。不会记录日志,所以执行速度很快,但不能通过rollback撤消操作(如果一不小心把一个表truncate掉,也是可以恢复的,只是不能通过rollback来恢复。TRUNCATE不会逐个清除用户数据块上的数据,而仅仅重置数据字典和元数据块上的元数据。也就是说,此时,其基本数据并未被破坏,而是被系统回收、等待被重新分配,因此,要恢复被TRUNCATE的数据,但是要快要数据库被重写前快速实施恢复,这样我们可以做到100%完全恢复)。truncate删除数据快速恢复办法见最后说明。
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
附加说明:truncate删除数据恢复办法
因为truncate是DDL语句,不是DML语句,所以不能闪回查询as of 来恢复
恢复数据:
SQL> connect sys/mzl as sysdba
已连接。
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount
ORACLE 例程已经启动。
Total System Global Area 272629760 bytes
Fixed Size 1248476 bytes
Variable Size 117441316 bytes
Database Buffers 146800640 bytes
Redo Buffers 7139328 bytes
数据库装载完毕。
SQL> flash database to timestamp
SP2-0734: 未知的命令开头 “flash data…” - 忽略了剩余的行。
SQL> flashback database to timestamp
2 to_timestamp(‘2008-06-08 18:22:33’,’yyyy-mm-dd hh24:mi:ss’);
闪回完成。
SQL> alter database open read only;
数据库已更改。
SQL> select * from scott.dept;
DEPTNO DNAME LOC
50 support denver
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS beijing
如果数据恢复不够理想,可以关闭数据库继续进行恢复。 如果用’alter database open resetlogs’打开,在想关闭数据库用flashback恢复就不行了