oracle10g/11g 新特性三(Flashback Table)

Preparing Your Database for Flashback

1.Creating an undo tablespace

2.Enabling Automatic Undo Management

3.Specifying sersus guaranteeing undo retention

  Default database initialization parameters

  UNDO_MANAGMENT='AUTO'

  UNDO_TABLESPACE='UNDOTBS1'

  UNOD_RETENTION=900 要设置guarantee时　，才可靠

SQL> alter tablespace undotbs1 retention guarantee;                                                           
Tablespace altered.
SQL> select tablespace_name,contents,retention from dba_tablespaces;                                                        

TABLESPACE_NAME                CONTENTS  RETENTION
------------------------------ --------- -----------
SYSTEM                         PERMANENT NOT APPLY
UNDOTBS1                       UNDO      GUARANTEE
SYSAUX                         PERMANENT NOT APPLY
TEMP                           TEMPORARY NOT APPLY
USERS                          PERMANENT NOT APPLY
APP1                           PERMANENT NOT APPLY
APP2                           PERMANENT NOT APPLY
APP3                           PERMANENT NOT APPLY
PERFSTAT                       PERMANENT NOT APPLY

Flashback Query

select employee_id, salary from employees as of timestamp <T1> where empoyee_id=200

select employee_id, salary from employees as of scn 7469 where empoyee_id=200

SQL> insert into ta values('1','wwww');     commit;                                  
QL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;             

TO_CHAR(SYSDATE,'YY
-------------------
2015-05-02 01:23:55

SQL> insert into ta values('2','do');      commit;                                   

SQL> select * from ta  as of timestamp to_timestamp('2015-05-02 01:23:55','yyyy-mm-dd hh24:mi:ss');                          

ID                   NAME
-------------------- --------------------
1                    wwww

SQL> select * from ta;                                                                                                       

ID                   NAME
-------------------- --------------------
1                    wwww
2                    dddd

可以拼写SQL 语句把数据改回来　可以带上　as of timestamp <T1> 　之类的去原表取数据

Flashback Version Query

还有伪列:versions_starttime, versions_endtime  

Version Query 使用限制

the VERSION clause cannot be used to query:

 1 External tables

 2.Temporary tables

 3.Fixed tables

 4.views

The VERSIONS clause cannot span ddl commands

Segment shrink operatons are filtered out

Flashback Table Concern

1.object privilege:  FLASHBACK ANY TABLE  or  FLASHBACK , SELECT, INSERT,DELETE,ALTER

2.You must enable row movement on the table

    ALTER TABLE empoyees ENABLE ROW MOVEMENT;

3.闪回表 不需要 重启数据库 可以online进行

Flashback Table 限制条件

1.FLASHBACK TABLE 执行就像一个事物，获得 DML 排他锁

2.统计指标不会被闪回

3.当前的索引和依赖objects 会被维护

4.不能在系统表上执行

5.不能跨越 DDL操作

6.产生undo 和redo 数据

7.系统表不能被闪回  把普通表建立在sys用户下的表 也算系统表 ，而不管是否在系统表空间

虽然是ddl语句，但是也能闪回到  建索引以前，而且索引也存在。

truncate 语句 不能闪回

flashback_transaction_query

grant select any transaction to scott;

select table_name,operation,undo_sql from flashback_transaction_query;

Flash Transaction query: 考虑的问题

1.DDL操作 看到的是 数据库字典的变化

2.删除的对象看到的是 对象的ID

3.删除的用户看到的是 用户ID

Flashing back a transaction

撤销事务流程

1.Viewing data in a table

2.观察逻辑问题

3.用falshback Transaction

   a.执行查询

   b.选择一个事务

   c. 闪回事务(没有冲突)

   d.尝试其他闪回操作(冲突存在)

4.查询闪回事务结果

Flashback Transaction Wizard

需要flash_transaction功能需要增加附加日志

alter database add supplemental log data;

alter database add supplemental log data(primary key) columns;

select log_mode,supplemental_log_data_min from v$database;

grant execute on dbms_flashback to test; 需要这个权限

select xid, commit_timestamp,undo_sql  from falshback_transaction_query where table_owner='TEST' and table_name='EMP';
DBMS_FLASHBACK.TRANSACTION_BACKOUT(1,V_XID);

DBMS_FLASHBACK.TRANSACTION_BACKOUT(1,V_XID,DBMS_FLASHBACK.CASCADE) 如果有依赖的事务，一起回滚

                                           DBMS_FLASHBACK.NOCASCADE_FORCE

                                           DBMS_FLASHBACK.NONCONFLICT_ONLY

如果错了： rollback  如果对了：  commit;