drop table可以还原吗

发布网友发布时间：2022-05-06 19:33

共2个回答

懂视网时间：2022-05-06 23:54

Oracle官方文档：Oracle Database SQL Language Reference PURGE Specify PURGE if you want to drop the table and release the space associated with it in a single step. If you specify PURGE, then the database does not place the table and its de

Oracle官方文档：Oracle Database SQL Language Reference

PURGE

Specify PURGE if you want to drop the table and release the space associated with it in a single step. If you specify PURGE, then the database does not place the table and its dependent objects into the recycle bin.

Caution:

You cannot roll back a DROP TABLE statement with the PURGE clause, nor can you recover the table if you have dropped it with the PURGE clause.

Using this clause is equivalent to first dropping the table and then purging it from the recycle bin. This clause lets you save one step in the process. It also provides enhanced security if you want to prevent sensitive material from appearing in the recycle bin.

-------------------------------------

1、drop table zml后,zml表被放入recycle bin.可以通过flashback table zml to before drop恢复drop掉的表，表中数据也可以被恢复。

2、drop table zml purge后zml表被直接删除，不可恢复

--------实例：----------

---------------drop table zml-------------------------
SQL> show parameter recycle;

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_recycle   string
db_recycle_cache_size  big integer 0
recyclebin    string on
---
SQL> purge recyclebin;

回收站已清空。

SQL> show recyclebin;
SQL> create table zml(id int);

表已创建。

SQL> insert into zml values(1);

已创建 1 行。

SQL> drop table zml;

表已删除。

SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME  OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------

ZML  BIN$2cOZCZj0Ry27btpd7ymTHg==$0 TABLE 2014-04-23:21:56:58
SQL> flashback table zml to before drop;

闪回完成。

SQL> select * from zml;

 ID
----------
  1

SQL>
-----------------drop table zml1 purge-------------------------
SQL> show recyclebin;
SQL> create table zml1(id int);

表已创建。

SQL> insert into zml1 values(2);

已创建 1 行。

SQL> drop table zml1 purge;

表已删除。

SQL> show recyclebin;
SQL>

热心网友时间：2022-05-06 21:02

一、drop表

执行drop table xx 语句

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;

清除回收站里的信息