oracle中truncate和delete命令有何区别? oracle中truncate和delete命令有何区别? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 truncate无法恢复DELETE可以恢复 一个是DDL操作, 一个是DML操作, 两种操作在10G以后的flashback功能下都是可以恢复的 truncate不写日志,无法回滚delete要写日志 主要差别是1.TRUNCATE高水位线下降了. 2.TRUNCATE没REDO在SQLPLUS下试试下面语句就知道了SQL> set autotrace traceonlySQL> create table t1 as select * from dba_objects;表已创建。SQL> delete from t1;已删除30484行。执行计划---------------------------------------------------------- 0 DELETE STATEMENT Optimizer=CHOOSE 1 0 DELETE OF 'T1' 2 1 TABLE ACCESS (FULL) OF 'T1'统计信息---------------------------------------------------------- 50 recursive calls 33930 db block gets 444 consistent gets 394 physical reads 10604052 redo size 715 bytes sent via SQL*Net to client 541 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 30484 rows processedSQL> commit;提交完成。SQL> select * from t1;未选定行执行计划---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'T1'统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 611 consistent gets 95 physical reads 11520 redo size 918 bytes sent via SQL*Net to client 361 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processedSQL> truncate table t1;表被截断。SQL> select * from t1;未选定行执行计划---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'T1'统计信息---------------------------------------------------------- 0 recursive calls 1 db block gets 5 consistent gets 0 physical reads 40 redo size 918 bytes sent via SQL*Net to client 361 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processedSQL> 菜鸟问一个不同数据库之间连接的问题 oracle 的多线程问题 xmanage 安装运行报错 导入数据时出错 Solaris man设置 如何修改Oracle8i(8.1.6.0.0)首选身份证明(OS is Win2000) oracle中有无象ms sql server的select top n * from tab的语句? 请问: 如何知道当前用户有没有dbstart和dbshut的权限 有某几个字段等于一个值得该如何查询? java调用有返回值的存储过程,一直报错 求解,谢谢 菜鸟 求一条SQL语句 急!! ora-01722无效数字
DELETE可以恢复
delete要写日志
在SQLPLUS下试试下面语句就知道了
SQL> set autotrace traceonly
SQL> create table t1 as select * from dba_objects;表已创建。SQL> delete from t1;已删除30484行。
执行计划
----------------------------------------------------------
0 DELETE STATEMENT Optimizer=CHOOSE
1 0 DELETE OF 'T1'
2 1 TABLE ACCESS (FULL) OF 'T1'
统计信息
----------------------------------------------------------
50 recursive calls
33930 db block gets
444 consistent gets
394 physical reads
10604052 redo size
715 bytes sent via SQL*Net to client
541 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
30484 rows processedSQL> commit;提交完成。SQL> select * from t1;未选定行
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'T1'
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
611 consistent gets
95 physical reads
11520 redo size
918 bytes sent via SQL*Net to client
361 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processedSQL> truncate table t1;表被截断。SQL> select * from t1;未选定行
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'T1'
统计信息
----------------------------------------------------------
0 recursive calls
1 db block gets
5 consistent gets
0 physical reads
40 redo size
918 bytes sent via SQL*Net to client
361 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processedSQL>