OceanBase FLASHBACK TABLE BEFORE DROP

描述

用于從回收站中恢復(fù)被刪除的表。

前置條件

回收站需要處于開啟狀態(tài)，可以通過?SHOW VARIABLES LIKE 'recyclebin';?來查看回收站是否開啟。

obclient>SHOW VARIABLES LIKE 'recyclebin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| recyclebin    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

如果回收站處于關(guān)閉狀態(tài)，可以通過?SET RECYCLEBIN = ON;?來開啟?；厥照局械谋頉]有被實際刪除，仍然會占用資源，如果需要徹底刪除，可以執(zhí)行?PURGE RECYCLEBIN;?

格式

 FLASHBACK TABLE object_name TO BEFORE DROP [RENAME to db_name.table_name];

參數(shù)解釋

示例

• 從回收站中恢復(fù)被刪除的表 t。

obclient>CREATE TABLE t(id INT PRIMARY KEY, k INT);
Query OK, 0 rows affected (0.04 sec)

obclient>INSERT INTO t VALUES(1,1);
Query OK, 1 row affected (0.00 sec)

obclient>SELECT * FROM t;
+----+------+
| id | k    |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.00 sec)

obclient>DROP TABLE t;
Query OK, 0 rows affected (0.01 sec)

obclient>SELECT * FROM t;
ORA-00942: table or view 'SYS.T' does not exist

obclient>SHOW RECYCLEBIN;
+--------------------------------+---------------+-------+----------------------------+
| OBJECT_NAME                    | ORIGINAL_NAME | TYPE  | CREATETIME                 |
+--------------------------------+---------------+-------+----------------------------+
| RECYCLE_$_2_1616038871797032 | T             | TABLE | 18-MAR-21 11.41.11.797691 AM|
+--------------------------------+---------------+-------+----------------------------+
1 row in set (0.00 sec)

obclient>FLASHBACK TABLE t TO BEFORE DROP;
Query OK, 0 rows affected (0.01 sec)

obclient>SELECT * FROM t;
+----+------+
| id | k    |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.00 sec)