oracle 常用的闪回操作

flashback可操作类型
	Flashback table 
	Flashback database 
	Flashback query (version + trancation)
	
	
闪回表 Flashback table  ：
SQL> show  parameter recyclebin
NAME				     TYPE			       VALUE
------------------------------------ --------------------------------- ------------------------------
recyclebin			     string			       on
如果要打开回收站 (立刻生效)：
alter system set  recyclebin=off scope=spfile；
alter session set  recyclebin=off scope=spfile；

SQL> drop table t1;
SQL> select * From tab;
TNAME											   TABTYPE		  CLUSTERID
------------------------------------------------------------------------------------------ --------------------- ----------
DEPT											   TABLE
EMP											   TABLE
BONUS											   TABLE
SALGRADE										   TABLE
BIN$CBvugscIwNbgUAoK3ggO9Q==$0								   TABLE
T2											   TABLE
或者是
show recycle; 查看回收站.
SQL> show  recyclebin
ORIGINAL NAME	 RECYCLEBIN NAME		OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
T1		 BIN$CBvugscIwNbgUAoK3ggO9Q==$0 TABLE	     2014-11-18:14:09:02

我们可以看到t1表在回收站中，如果我们对这个表不用，可以用
purge recyclbin；
如果是无意删除可以通过闪回表，找会此表

flashback table t1 to before drop;
flashback table t1 to before drop rename to t7;
flashback table t4 to before drop rename to t7; 新改名不用加引号.
 flashback table "BIN$CBvugscIwNbgUAoK3ggO9Q==$0" to before drop;




Flashback query (version + trancation)
闪查的数据来自于回滚段  如果空间太小,闪查的维持时间就短,同时太小会有ORA-01555
		undo_retention=n（秒)参数就是数据维持在ROLLBACK_segment的不强制保持时间
		因为要做DML 就要先申请rollback空间 无法申请就无法继续做下去
		为了保持新事务正常运行 oracle默认采用了undo_retention不强制保持时间
		就是即使时间不到,因空间不足也可以备覆盖重用
闪查种类可分为
	1.闪回SCN或时间查询
	2.闪回事务查询
	3.闪回版本查询

	1.闪回时间查询	
SQL> create table t1 as select * From emp;
SQL> select * From t1 where ename='SCOTT';
     EMPNO ENAME			  JOB				     MGR HIREDATE	       SAL	 COMM	  DEPTNO
---------- ------------------------------ --------------------------- ---------- -------------- ---------- ---------- ----------
      7788 SCOTT                          ANALYST                           7566 19-4月 -87           3000                    20

SQL> UPDATE T1 SET SAL=SAL+1 WHERE ENAME='SCOTT';
SQL> COMMIT;	
SQL> select * from t1 as of timestamp  sysdate-17/1440 where ename='SCOTT';

     EMPNO ENAME			  JOB				     MGR HIREDATE	       SAL	 COMM	  DEPTNO
---------- ------------------------------ --------------------------- ---------- -------------- ---------- ---------- ----------
      7788 SCOTT                          ANALYST                           7566 19-4月 -87           3000                    20
      
      在此处我们可以看到数据被修改之前  SAL=3000

2.闪回scn时间查询	


在使用oralce闪回特性恢复数据时，有时需要知道SCN与时间的对应关系，该对应关系可以通过SMON_SCN_TIME视图得到
oracle数据库会每间隔5分钟向该表中写入一条数据，这个可以从上面查询到的结果中看出来
SQL>  select scn,to_char(time_dp,'yyyy-mm-dd hh24:mi:ss') time from sys.smon_scn_time where time_dp like '2014-11-18 15%' ;
	    SCN TIME
--------------- ---------------------------------------------------------
    49733519167 2014-11-18 15:51:38
    49733519333 2014-11-18 15:56:43
    49733518113 2014-11-18 15:19:49
    49733518272 2014-11-18 15:24:56
    49733518431 2014-11-18 15:30:04
    49733518680 2014-11-18 15:36:11
    49733518816 2014-11-18 15:41:18
    49733518972 2014-11-18 15:46:27
    49733517608 2014-11-18 15:04:35
    49733517742 2014-11-18 15:09:42
    49733517978 2014-11-18 15:14:46
    49733518633 2014-11-18 15:35:36
12 rows selected.

SQL> select * from t1 as of scn  49733518680 where ename='SCOTT';
     EMPNO ENAME			  JOB				     MGR HIREDATE	       SAL	 COMM	  DEPTNO
---------- ------------------------------ --------------------------- ---------- -------------- ---------- ---------- ----------
      7788 SCOTT                          ANALYST                           7566 19-4月 -87           3000                    20

      在此处我们可以看到数据被修改之前  SAL=3000


3 闪回版本查询
SQL> select versions_starttime, versions_endtime, versions_xid, versions_operation,ename,sal 
  2  from scott.t1 versions between timestamp to_timestamp('2014-11-18 15:36:00', 'YYYY-MM-DD HH24:MI:SS') and maxvalue order by VERSIONS_STARTTIME ;

VERSIONS_STARTTIME   VERSIONS_ENDTIME								 VERSIONS_XID	  VER ENAME				    SAL
-------------------- --------------------------------------------------------------------------- ---------------- --- ------------------------------ ----------
18-11月-14 03.36.32                                                                              0300130022060000 U   SCOTT                                3001
下午

														      ALLEN				   1600
														      WARD				   1250
														      JONES				   2975
														      MARTIN				   1250
														      BLAKE				   2850
														      CLARK				   2450
                     18-11月-14 03.36.32 下午                                                                         SCOTT                                3000
														      KING				   5000
														      TURNER				   1500
														      ADAMS				   1100
														      JAMES				    950
														      FORD				   3000
														      MILLER				   1300
														      SMITH				    800

15 rows selected.


4  闪回事物查询
SQL> select xid,COMMIT_SCN,COMMIT_TIMESTAMP,OPERATION,UNDO_SQL 
  2         from flashback_transaction_query 
  3         where TABLE_NAME='T1' and TABLE_OWNER='SCOTT' and COMMIT_SCN >= 49733518680 and COMMIT_SCN <= 49733519680;



XID		 COMMIT_SCN COMMIT_TIMESTAMP	OPERATION
---------------- ---------- ------------------- ------------------------------------------------------------------------------------------------
UNDO_SQL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0300130022060000 4.9734E+10 2014-11-18 15:36:32 UPDATE
update "SCOTT"."T1" set "SAL" = '3000' where ROWID = 'AAAOa6AAEAAAAA8AAH';