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;