ITPub博客

flashback被drop掉的表

原创 Linux操作系统 作者:cqubityj 时间:2007-12-21 11:50:46 0 删除 编辑

1、开启recyclebin:alter system set recyclebin=on/alter session set recyclebin=on (10gR2以上)
2、flashback drop table的步骤:
(1) 查看dba_recyclebin,确认具体需要恢复的表
select owner,object_name,original_name,partition_name,type,ts_name,createtime,droptime,canundrop from dba_recyclebin;
(2) 恢复表(可以重命名):
如果只是需要表中的数据,则可以直接从recyclebin中取得。
如果表只drop过一次,可以用原对象名来恢复:flashback table original_tabname to before drop rename to new_tabname;
如果表drop过多次,要用recyclebin中的对象名来恢复:flashback table current_tabname to before drop rename to new_tabname.
(如果new_tabname已经存在,则flashback会报错,但不会影响原来new_tabname表)

flashback无法恢复全文索引(from yangtingkun).

http://yangtingkun.itpub.net/post/468/228085

SQL> create user user1 identified by abc default tablespace testtbs temporary tablespace temp quota unlimited on testtbs;

User created.

SQL>
SQL> grant connect,resource to user1;

Grant succeeded.

SQL> conn user1/abc  
Connected.

SQL> create table tab1 (id number);

Table created.

SQL> insert into tab1 values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> drop table tab1;

Table dropped.

SQL> create table tab1(name varchar2(10),seq number);

Table created.

SQL> insert into tab1 values ('test1',1);

1 row created.

SQL> commit;

Commit complete.

SQL> drop table tab1;

Table dropped.

下面恢复表
先查询recyclebin
select object_name,original_name,partition_name,type,ts_name,createtime,droptime,can_undrop from user_recyclebin;
SQL> select object_name,original_name,partition_name,type,ts_name,createtime,droptime,can_undrop from user_recyclebin;

OBJECT_NAME                    ORIGINAL_N PARTITION_ TYPE       TS_NAME    CREATETIME           DROPTIME             CAN_UN
------------------------------ ---------- ---------- ---------- ---------- -------------------- -------------------- ------
BIN$QcLhBBNUt9HgQAB/AQAeTw==$0 TAB1                  TABLE      TESTTBS    2007-12-21:10:45:51  2007-12-21:10:46:01  YES
BIN$QcLhBBNVt9HgQAB/AQAeTw==$0 TAB1                  TABLE      TESTTBS    2007-12-21:10:46:38  2007-12-21:10:47:01  YES
查询先建的表:

SQL> select * from "BIN$QcLhBBNUt9HgQAB/AQAeTw==$0";

        ID
----------
         1
查询后建的表:
SQL> select * from "BIN$QcLhBBNVt9HgQAB/AQAeTw==$0";

NAME                        SEQ
-------------------- ----------
test1                         1

恢复先建的表为new_tabl:
SQL> flashback table "BIN$QcLhBBNUt9HgQAB/AQAeTw==$0" to before drop rename to new_tab1;

Flashback complete.

SQL> select * from new_tab1;

        ID
----------
         1
 
恢复后建的表为new_tab2:
SQL> flashback table "BIN$QcLhBBNVt9HgQAB/AQAeTw==$0" to before drop rename to new_tab2;

Flashback complete.

SQL> select * from new_tab2;

NAME                        SEQ
-------------------- ----------
test1                         1

下面看看rename to的表已经存在的情况:
SQL> drop table new_tab1;

Table dropped.

SQL> select object_name,original_name,partition_name,type,ts_name,createtime,droptime,can_undrop from user_recyclebin;

OBJECT_NAME                    ORIGINAL_N PARTITION_ TYPE       TS_NAME    CREATETIME           DROPTIME             CAN_UN
------------------------------ ---------- ---------- ---------- ---------- -------------------- -------------------- ------
BIN$QcLhBBNWt9HgQAB/AQAeTw==$0 NEW_TAB1              TABLE      TESTTBS    2007-12-21:10:45:51  2007-12-21:11:03:14  YES

SQL> create table tab1 (tdate date);

Table created.

SQL> insert into tab1 values (sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> flashback table "BIN$QcLhBBNWt9HgQAB/AQAeTw==$0" to before drop rename to tab1;
flashback table "BIN$QcLhBBNWt9HgQAB/AQAeTw==$0" to before drop rename to tab1
*
ERROR at line 1:
ORA-38312: original name is used by an existing object

flashback出错,因为tab1表已经存在.

SQL> select * from tab1;

TDATE
------------
21-DEC-07

执行flashback并没有影响到已经存在的表。

SQL>
SQL> select * from "BIN$QcLhBBNWt9HgQAB/AQAeTw==$0";

        ID
----------
         1

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/228190/viewspace-1043/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-19

  • 博文量
    133
  • 访问量
    423298