：oracle数据库中删除表后创建同名表，如何闪回删除后的表？

denglj

问题描述：oracle数据库中删除表后创建同名表，如何闪回删除后的表？

数据库：oracle11.2.0.4 64位

系统：centos7.9 64位

1、模拟异常

SQL>select count(*) from test;

  COUNT(*)

----------

    256000

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

no rowsselected

SQL>drop table test;

Tabledropped.

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==$0TEST                            DROP      TABLE

创建同名表.

SQL>create table test (id number(6,2),

  2                     name varchar2(20));

Tablecreated.

SQL>begin

  2   for i in 1..20 loop

  3   insert into test values (i,'a' || i);

  4   end loop;

  5  end;

  6  /

PL/SQLprocedure successfully completed.

SQL>select count(*) from test;

  COUNT(*)

----------

        20

SQL>commit;

Commitcomplete.

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

OBJECT_NAME                    ORIGINAL_NAME                    OPERATION TYPE

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

BIN$7LGIbMiPDmXgU9mFqMA7vQ==$0TEST                            DROP      TABLE

SQL>flashback table test to before drop;

flashbacktable test to before drop

*

ERROR atline 1:

ORA-38312:original name is used by an existing object

说明：如上所示，闪回表时出现ORA-38312告警.

2、解决方案2.1、指定新表名

SQL>flashback table test to before drop rename to test01;

Flashbackcomplete.

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;

Tabledropped.

SQL>create table test (id number(6,2),

  2                     name varchar2(20));

Tablecreated.

SQL>begin

  2   for i in 1..20 loop

  3   insert into test values (i,'a' || i);

  4   end loop;

  5  end;

  6  /

PL/SQLprocedure successfully completed.

SQL>commit;

Commitcomplete.

SQL>select table_name from user_tables;

TABLE_NAME

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

TEST

TEST01

对表test重新命令为其它表名.

SQL>alter table test rename to test02;

Tablealtered.

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

OBJECT_NAME                    ORIGINAL_NAME                    OPERATION TYPE

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

BIN$7LGIbMiQDmXgU9mFqMA7vQ==$0TEST                            DROP      TABLE

SQL>flashback table test to before drop;

Flashbackcomplete.

SQL>select table_name from user_tables;

TABLE_NAME

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

TEST02

TEST

TEST01