Oracle12C中如何恢复被删除的表

登录sqlplus

[oracle@vm100 ~]$ sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Thu May 12 14:36:38 2022

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

SQL> connect sys@oracle12 as sysdba;
Enter password: 
Connected.

创建测试用户并授权

SQL> create user c##jihui identified by 111111;

User created.

SQL> grant dba to c##jihui;

Grant succeeded.

登录测试用户并创建测试表

SQL> connect c##jihui@oracle12;
Enter password: 
Connected.
SQL> create table t_user(f_userid int, f_username varchar(20));

Table created.

SQL> insert into t_user values(1, 'jihui');

1 row created.

SQL> select * from t_user;

  F_USERID F_USERNAME
---------- --------------------
	 1 jihui

SQL> create table t_dept(f_deptid int, f_deptname varchar(20));

Table created.

SQL> insert into t_dept values(1, 'dev');

1 row created.

SQL> select * from t_dept;

  F_DEPTID F_DEPTNAME
---------- --------------------
	 1 dev

删除测试表，进行恢复测试

SQL> drop table t_user;

Table dropped.

SQL> select * from t_user;
select * from t_user
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select original_name,droptime from recyclebin where original_name = 'T_USER';

ORIGINAL_NAME
--------------------------------------------------------------------------------
DROPTIME
-------------------
T_USER
2022-05-12:14:39:33

SQL> insert into t_dept values(2, 'test');

1 row created.

SQL> select * from t_dept;

  F_DEPTID F_DEPTNAME
---------- --------------------
	 1 dev
	 2 test

SQL> flashback table t_user to before drop;

Flashback complete.

SQL> select * from t_user;

  F_USERID F_USERNAME
---------- --------------------
	 1 jihui

SQL> select * from t_dept;

  F_DEPTID F_DEPTNAME
---------- --------------------
	 1 dev
	 2 test

经过测试，在Oracle中，处理表被误删除非常简单。

1. 可以通过查询recyclebin，确定表的精确删除时间。
2. 可以通过flashback快速恢复被删除的表数据，且不影响该表被删除后，其它表的新增数据。