Heim >Datenbank >MySQL-Tutorial >Oracle Flashback table的使用

Oracle Flashback table的使用

WBOY
WBOYOriginal
2016-06-07 17:33:331519Durchsuche

Oracle Flashback table的使用 Oracle确保recyclebin是打开的 SQLgt; show parameter recyclebin NAME

Oracle Flashback table的使用

Oracle确保recyclebin是打开的


SQL> show parameter recyclebin

NAME                                TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                          string      ON创建一张表


SQL> create table tab01(id int);

Table created.

SQL> insert into tab01 values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from tab01;

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

SQL> create index ind_id on tab01(id);

Index created.删除表TAB01


18:18:26 SQL> select index_name from ind where table_name='TAB01';

INDEX_NAME
------------------------------
IND_ID

18:18:33 SQL> drop table tab01;

Table dropped.

18:18:41 SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TAB01            BIN$7E8nf4eZQZzgQKjACzgfMg==$0 TABLE        2013-11-29:18:18:41
18:18:43 SQL> select index_name from ind where table_name='TAB01';

no rows selected

18:18:50 SQL> select * from tab01;
select * from tab01
              *
ERROR at line 1:
ORA-00942: table or view does not exist
发现TAB01上的index也被rename,flashback TAB01


18:19:41 SQL> flashback table tab01 to before drop;

Flashback complete.

18:19:51 SQL> select * from tab01;

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

18:19:54 SQL> select index_name from ind where table_name='TAB01';

INDEX_NAME
------------------------------
BIN$7E8nf4eYQZzgQKjACzgfMg==$0rename index


18:23:09 SQL> ALTER INDEX  "BIN$7E8nf4eYQZzgQKjACzgfMg==$0" RENAME TO IDX_ID;

Index altered.

18:23:45 SQL> select index_name,status from ind where table_name='TAB01';

INDEX_NAME                    STATUS
------------------------------ --------
IDX_ID                        VALID

 

如果多次删除同一张表也可以指定recyclebin的名称flashback

18:25:29 SQL> select * from tab01;

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

18:25:36 SQL> drop table tab01;

Table dropped.

18:25:50 SQL> create table tab01(id int);

Table created.

18:26:17 SQL> insert into tab01 values (2);

1 row created.

18:26:30 SQL> commit;

Commit complete.

18:26:33 SQL> select * from tab01;

        ID
----------
        2

18:26:37 SQL> drop table tab01;

Table dropped.

18:26:43 SQL> create table tab01(id int);

Table created.

18:26:46 SQL> insert into tab01 values(3);

1 row created.

18:26:55 SQL> commit;

Commit complete.

18:26:56 SQL> select * from tab01;

        ID
----------
        3

18:26:59 SQL> drop table tab01;

Table dropped.

18:27:02 SQL> select * from tab01;
select * from tab01
              *
ERROR at line 1:
ORA-00942: table or view does not exist18:27:10 SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TAB01            BIN$7E8nf4edQZzgQKjACzgfMg==$0 TABLE        2013-11-29:18:27:02
TAB01            BIN$7E8nf4ecQZzgQKjACzgfMg==$0 TABLE        2013-11-29:18:26:43
TAB01            BIN$7E8nf4ebQZzgQKjACzgfMg==$0 TABLE        2013-11-29:18:25:50flashback tab01中id值为2的


18:27:51 SQL> flashback table "BIN$7E8nf4ecQZzgQKjACzgfMg==$0" to before drop;

Flashback complete.

18:29:17 SQL> select * from tab01;

        ID
----------
        2
flashback同时rename


18:30:54 SQL> flashback table "BIN$7E8nf4edQZzgQKjACzgfMg==$0" to before drop rename to tab02;

Flashback complete.

18:31:17 SQL> select * from tab02;

        ID
----------
        3
也可以根据timestamp或者scn进行表级别的基于时间点的恢复,需要开启row movement

18:32:42 SQL> create table tab03(id int);

Table created.

18:32:55 SQL> insert into tab03 values(1);

1 row created.

18:33:08 SQL> insert into tab03 values(2);

1 row created.

18:33:10 SQL> insert into tab03 values(3);

1 row created.

18:33:12 SQL> commit;

Commit complete.

18:33:14 SQL>
18:33:16 SQL> insert into tab03 values(4);

1 row created.

18:33:23 SQL> commit;

Commit complete.

18:33:25 SQL> select * from tab03;

Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn