flashback version query
版本
SQL> create table t1(x int);
SQL> insert into t1 values (1);
SQL> commit;
SQL> update t1 set x=2;
SQL> commit;
SQL> update t1 set x=3;
SQL> commit;
SQL> update t1 set x=4;
SQL> commit;
SQL> select versions_starttime, versions_endtime, versions_xid, versions_operation, x
from t1
versions between scn minvalue and maxvalue
order by versions_starttime;
versions between timestamp to_timestamp('2015-10-28 9:00:00', 'yyyy-mm-dd hh24:mi:ss') and to_timestamp('2015-10-28 10:00:00', 'yyyy-mm-dd hh24:mi:ss')
SQL> truncate table t1; 物理结构改变，查询失败
flashback table
SQL> conn user01/password
SQL> create table my_dept(deptno int primary key, dname varchar2(20));
SQL> create table my_emp(empno int primary key, deptno int references my_dept);
SQL> insert into my_dept values (10, 'sales');
SQL> insert into my_emp values (100, 10);
SQL> commit;
SQL> select dbms_flashback.get_system_change_number from dual;
SQL> delete my_emp;
SQL> delete my_dept;
SQL> commit;
SQL> alter table my_dept enable row movement; 行
SQL> alter table my_emp enable row movement;
SQL> flashback table my_emp to scn 1451706; 失败
SQL> flashback table my_dept to scn 1451706;
SQL> flashback table my_emp to scn 1451706;
SQL> select INDEX_NAME, STATUS from user_indexes where table_name='MY_EMP';自动维护索引
SQL> select INDEX_NAME, STATUS from user_indexes where table_name='MY_DEPT';
SQL> truncate table my_emp;
SQL> flashback table my_emp to scn 1451706; 失败
以上 撤销表空间
flashback drop
回收站
SQL> show parameter recyclebin
SQL> purge recyclebin;清空
SQL> create tablespace tbs01 datafile '/home/oracle/tbs01.dbf' size 5M;
SQL> create table t1 tablespace tbs01 as select * from dba_objects where rownum<=20000;
SQL> create index t1_object_id_idx on t1(object_id) tablespace tbs01;
SQL> select INDEX_NAME from user_indexes where TABLE_NAME='T1';
SQL> drop table t1;
SQL> select table_name from user_tables;
SQL> show recyclebin
SQL> select object_name, original_name, type, droptime from user_recyclebin; 包含index
SQL> select count(*) from "BIN$IyKOcy5jPo7gUwEAqMCBEg==$0";
SQL> flashback table t1 to before drop;
SQL> select INDEX_NAME from user_indexes where TABLE_NAME='T1';
SQL> alter index "BIN$LRyc7hA1JaPgUwEAqMDzWw==$0" rename to T1_OBJECT_ID_IDX; 恢复index名称
重名的处理：
SQL> flashback table "BIN$IyKOcy5jPo7gUwEAqMCBEg==$0" to before drop;
SQL> flashback table t1 to before drop rename to t2;
SQL> drop table t1;
SQL> show recyclebin 在回收站中
SQL> create table t2 tablespace tbs01 as select * from dba_objects where rownum<=30000;
SQL> show recyclebin t1被覆盖
SQL> drop table t2 purge;
SQL> purge recyclebin
flashback transaction query
SQL> alter database add supplemental log data;
SQL> alter database add supplemental log data (primary key) columns;
SQL> create table t1(x int);
SQL> insert into t1 values (1);
SQL> commit;
SQL> update t1 set x=11 where x=1; 误操作的事务
SQL> commit;
SQL> insert into t1 values (2);
SQL> commit;
select versions_starttime, versions_endtime, versions_xid, versions_operation, x
from t1
versions between scn minvalue and maxvalue
order by versions_starttime; 获取误操作事务的xid
SQL> select UNDO_SQL, OPERATION from flashback_transaction_query where xid='02000F0059040000';