Drop、Delete与Trunc 的区别和应用

在Oracle数据库中，DROP 、 DELETE、TRUNCATE 都可以用来处理表的数据，但是功能和效果是不同的。以下是它们之间的区别以及如何来进行恢复数据。

1. DROP 表

• 功能：DROP 表会完全删除表的结构和数据，表以及相关的索引、约束等都会被永久删除，无法通过简单的 SQL 语句恢复。
• 语法：

  DROP TABLE table_name;
  

• 后果：使用 DROP TABLE 后，表及其所有数据将被永久删除，除非使用了数据库的备份或闪回技术来恢复。

2. DELETE 表

• 功能：DELETE 语句用于删除表中的数据，但保留表的结构（表定义、索引、约束等）。你可以选择性地删除所有数据或根据条件删除部分数据。
• 语法：

  DELETE FROM table_name WHERE id='1001';
  

  或删除所有数据：

  DELETE FROM table_name;
  

• 后果：DELETE 仅删除数据，表结构仍然保留，可以继续向表中插入数据。如果没有使用 WHERE 条件，DELETE 会删除表中的所有数据。被删除的数据可以通过 COMMIT 前的 ROLLBACK 操作来恢复。

3. TRUNCATE 表

• 功能：TRUNCATE 也是删除表中所有数据，但比 DELETE 快且无法回滚。表结构保留。
• 影响：无法通过 ROLLBACK 恢复，数据删除后直接提交。
• 语法：

  TRUNCATE TABLE table_name;
  

4. 恢复数据

• 对于 DROP Table：

  • 如果你误删了表，可以通过 Oracle 的 闪回技术（Flashback Technology） 来恢复表，但前提是数据库启用了闪回功能，且满足相关条件。
  • 闪回表 示例：

    FLASHBACK TABLE table_name TO BEFORE DROP;
    

    这种方法可以恢复最近被删除的表。

• 对于 DELETE 和 TRUNCATE Table：

  • 如果在执行 DELETE 语句后尚未提交（即未执行 COMMIT），可以使用 ROLLBACK 命令来撤销删除操作：

    ROLLBACK;
    

  • 如果已经提交了事务，数据将无法通过 ROLLBACK 恢复。但可以通过 闪回查询（Flashback Query） 来恢复表的某个时间点的数据，前提是表的行移动（ROW MOVEMENT）已启用。
    • 闪回查询 ：

      SELECT * FROM table_name AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR);
      

    • 将闪回查询结果插入表中 ：

      INSERT INTO SELECT * FROM table_name AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR);
      

• 表空间恢复：

  • 如果需要恢复一个被 DROP 的表，且闪回不可用，还可以使用数据泵或其他备份和恢复工具来恢复整个表空间。

• 从备份恢复：

  • 如果没有使用闪回技术或启用了相应的恢复机制，恢复方法通常是从全库或部分备份中还原被删除的表或数据。

5. 总结

• DROP 会删除整个表结构及数据，无法回滚，但可以使用闪回或备份恢复。
• DELETE 只删除数据而保留表结构，操作可以回滚（如果未提交），可通过闪回或备份恢复数据。
• TRUNCATE 快速删除表中所有数据，不可回滚，恢复手段同 DELETE。