当前位置:  首页  >  PHP教程  >  PHP 应用  >  知识库

OracleFlashbacktable的使用

OracleFlashbacktable的使用Oracle确保recyclebin是打开的SQLgt;showparameterrecyclebinNAME

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;

吐了个 "CAO" !
扫码关注 PHP1 官方微信号
PHP1.CN | 中国最专业的PHP中文社区 | PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | PHP问答
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved PHP1.CN 第一PHP社区 版权所有