Oracle Flashback table的使用
来源:互联网 发布:iphone照片导出到mac 编辑:程序博客网 时间:2024/05/17 05:17
确保recyclebin是打开的
SQL> show parameter recyclebinNAME 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---------- 1SQL> 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_ID18:18:33 SQL> drop table tab01;Table dropped.18:18:41 SQL> show recyclebinORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME---------------- ------------------------------ ------------ -------------------TAB01 BIN$7E8nf4eZQZzgQKjACzgfMg==$0 TABLE 2013-11-29:18:18:4118:18:43 SQL> select index_name from ind where table_name='TAB01';no rows selected18: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---------- 118: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
18:25:29 SQL> select * from tab01; ID---------- 118: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---------- 218: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---------- 318: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 exist
18:27:10 SQL> show recyclebinORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME---------------- ------------------------------ ------------ -------------------TAB01 BIN$7E8nf4edQZzgQKjACzgfMg==$0 TABLE 2013-11-29:18:27:02TAB01 BIN$7E8nf4ecQZzgQKjACzgfMg==$0 TABLE 2013-11-29:18:26:43TAB01 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---------- 2flashback同时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; ID---------- 1 2 3 4
18:35:33 SQL> FLASHBACK TABLE TAB03 TO timestamp to_timestamp('2013-11-29 18:33:25','yyyy-mm-dd hh24:mi:ss');Flashback complete.18:35:39 SQL> select * from tab03; ID---------- 1 2 318:35:40 SQL> FLASHBACK TABLE TAB03 TO timestamp to_timestamp('2013-11-29 18:33:30','yyyy-mm-dd hh24:mi:ss');Flashback complete.18:35:54 SQL> select * from tab03; ID---------- 1 2 3 418:35:55 SQL> FLASHBACK TABLE TAB03 TO timestamp to_timestamp('2013-11-29 18:33:25','yyyy-mm-dd hh24:mi:ss');Flashback complete.18:35:59 SQL> select * from tab03; ID---------- 1 2 3
- Oracle Flashback table的使用
- oracle flashback 的使用
- Oracle Flashback之flashback table
- Oracle Flashback之flashback table
- Oracle flashback table简介
- flashback table 命令使用
- Oracle Flashback技术之Flashback Table
- Oracle Flashback之Flashback table和flashback drop
- Oracle 10g flashback table
- Oracle 10g flashback table 索引名带来的变化
- Oracle中Flashback Query的使用
- Flashback Table的一个案例
- Oracle 闪回特性(Flashback Query、Flashback Table)
- Oracle 闪回特性(Flashback Query、Flashback Table)
- Oracle 闪回特性(Flashback Query、Flashback Table)
- Oracle 闪回特性(Flashback Query、Flashback Table)
- Oracle 闪回特性(Flashback Query、Flashback Table)
- 再学oracle 10g flashback table
- 农夫山泉:HANA应用在企业中层层展现
- windows下Qt5.1 for android开发环境配置
- 淘宝应对"双11"的技术架构分析
- Pig 0.11 里 REGEX_EXTRACT() 中正則表達式的正確用法
- C/C++的20个位运算技巧
- Oracle Flashback table的使用
- C++实现一个LRU Cache
- CDH——hadoop
- 字符串相关
- AspNet_webformaspx页面_Session
- nopCommerce的配置以及汉化
- hdu-2047-阿牛的EOF牛肉串
- declare-styleable:自定义控件的属性
- freopen("*.txt","r",'"stdin"的含义