oracle数据库中删除表后创建同名表,如何闪回删除后的表?

问题描述:oracle数据库中删除表后创建同名表,如何闪回删除后的表?
数据库:oracle 11.2.0.4 64位
系统:centos 7.9 64位
1、模拟异常
SQL> select count(*) from test;

COUNT(*)
----------
256000

SQL> select object_name,original_name,operation,type from user_recyclebin;

no rows selected

SQL> drop table test;

Table dropped.

SQL> set line 200
SQL> col type for a15
SQL> select object_name,original_name,operation,type from user_recyclebin

OBJECT_NAME ORIGINAL_NAME OPERATION TYPE
------------------------------ -------------------------------- --------- ---------------
BIN$7LGIbMiPDmXgU9mFqMA7vQ==$0 TEST DROP TABLE
创建同名表.
SQL> create table test (id number(6,2),
2 name varchar2(20));

Table created.

SQL> begin
2 for i in 1..20 loop
3 insert into test values (i,'a' || i);
4 end loop;
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> select count(*) from test;

COUNT(*)
----------
20

SQL> commit;

Commit complete.

SQL> select object_name,original_name,operation,type from user_recyclebin;

OBJECT_NAME ORIGINAL_NAME OPERATION TYPE
------------------------------ -------------------------------- --------- ---------------
BIN$7LGIbMiPDmXgU9mFqMA7vQ==$0 TEST DROP TABLE

SQL> flashback table test to before drop;
flashback table test to before drop
*
ERROR at line 1:
ORA-38312: original name is used by an existing object
说明:如上所示,告警.
2、解决方案
2.1、指定新表名
SQL> flashback table test to before drop rename to test01;

Flashback complete.

SQL> select count(*) from test01;

COUNT(*)
----------
256000

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
TEST
TEST01
2.2、rename新表
SQL> drop table test;

Table dropped.

SQL> create table test (id number(6,2),
2 name varchar2(20));

Table created.

SQL> begin
2 for i in 1..20 loop
3 insert into test values (i,'a' || i);
4 end loop;
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
TEST
TEST01
对表test重新命令为其它表名.
SQL> alter table test rename to test02;

Table altered.

SQL> select object_name,original_name,operation,type from user_recyclebin;

OBJECT_NAME ORIGINAL_NAME OPERATION TYPE
------------------------------ -------------------------------- --------- ---------------
BIN$7LGIbMiQDmXgU9mFqMA7vQ==$0 TEST DROP TABLE

SQL> flashback table test to before drop;

Flashback complete.

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
TEST02
TEST
TEST01