oracle课堂笔记---第二十八天

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';