重庆思庄Oracle、Redhat认证学习论坛

标题: ：oracle数据库中删除表后创建同名表，如何闪回删除后的表？ [打印本页]

作者: denglj 时间: 2022-11-10 10:43
标题: ：oracle数据库中删除表后创建同名表，如何闪回删除后的表？

问题描述：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(*)

----------

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

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)