闪回查询和闪回版本查询实验

--闪回查询和闪回版本查询实验


--准备数据库以进行闪回

• 创建还原表空间
• 启用自动还原管理
• 指定还原保留时间和保证还原保留时间
• 默认的数据库初始化参数

1. UNDO_MANAGEMENT=AUTO
2. UNDO_TABLESPACE='UNDOTBS1'
3. UNDO_RETENTION=900

闪回查询：用于查询指定时间点的所有数据。

--使用timestamp闪回查询
SYS@PROD1> show parameter undo

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
undo_management 		     string	 AUTO
undo_retention			     integer	 900
undo_tablespace 		     string	 UNDOTBS2

SYS@PROD1> conn scott/tiger
Connected.
SCOTT@PROD1> select * from dept as of timestamp sysdate-1;  --查找一天前dept表数据

    DEPTNO DNAME	  LOC
---------- -------------- -------------
	10 ACCOUNTING	  NEW YORK
	20 RESEARCH	  DALLAS
	30 SALES	  CHICAGO
	40 OPERATIONS	  BOSTON

SCOTT@PROD1> insert into dept values(50,'D50','L50');  

1 row created.

SCOTT@PROD1> commit;  --插入新数据并提交

Commit complete.

SCOTT@PROD1> select * from dept as of timestamp sysdate-1;  --查找一天前dept表数据无变化

    DEPTNO DNAME	  LOC
---------- -------------- -------------
	10 ACCOUNTING	  NEW YORK
	20 RESEARCH	  DALLAS
	30 SALES	  CHICAGO
	40 OPERATIONS	  BOSTON

SCOTT@PROD1> select * from dept;  --查找表发现表已更改

    DEPTNO DNAME	  LOC
---------- -------------- -------------
	50 D50		  L50
	10 ACCOUNTING	  NEW YORK
	20 RESEARCH	  DALLAS
	30 SALES	  CHICAGO
	40 OPERATIONS	  BOSTON

SCOTT@PROD1> select * from dept as of timestamp sysdate-1/2880;  --查找半分钟前数据

    DEPTNO DNAME	  LOC
---------- -------------- -------------
	50 D50		  L50
	10 ACCOUNTING	  NEW YORK
	20 RESEARCH	  DALLAS
	30 SALES	  CHICAGO
	40 OPERATIONS	  BOSTON

--使用scn闪回查询	
SCOTT@PROD1> select ora_rowscn,deptno from dept;

ORA_ROWSCN     DEPTNO
---------- ----------
4299950867	   50
    782748	   10
    782748	   20
    782748	   30
    782748	   40

SCOTT@PROD1> select * from dept as of scn 4299950866 ;

    DEPTNO DNAME	  LOC
---------- -------------- -------------
	10 ACCOUNTING	  NEW YORK
	20 RESEARCH	  DALLAS
	30 SALES	  CHICAGO
	40 OPERATIONS	  BOSTON

SCOTT@PROD1> select * from dept as of scn 4299950867;

    DEPTNO DNAME	  LOC
---------- -------------- -------------
	50 D50		  L50
	10 ACCOUNTING	  NEW YORK
	20 RESEARCH	  DALLAS
	30 SALES	  CHICAGO
	40 OPERATIONS	  BOSTON

--闪回版本查询
其中外部表、临时表、固定表、视图不能使用闪回版本查询。
不能跨DDL命令查询。

select versions_xid,versions_starttime,versions_endtime,versions_startscn,versions_endscn,versions_operation
from t_name versions between scn|timestamp minvalue and maxvalue
where ...

SCOTT@PROD1> delete dept where deptno=50;

1 row deleted.

SCOTT@PROD1> commit;

Commit complete.

SCOTT@PROD1> select versions_xid,versions_operation,versions_starttime,deptno from dept versions between timestamp minvalue and maxvalue where deptno='50';

VERSIONS_XID	 V VERSIONS_STARTTIME	      DEPTNO
---------------- - ---------------------- ----------
0D00030049040000 D 25-DEC-16 03.03.42 PM	  50
						  50


SCOTT@PROD1> select * from dept;

    DEPTNO DNAME	  LOC
---------- -------------- -------------
	10 ACCOUNTING	  NEW YORK
	20 RESEARCH	  DALLAS
	30 SALES	  CHICAGO
	40 OPERATIONS	  BOSTON
	
SCOTT@PROD1> select * from dept as of timestamp to_timestamp('2016-12-25 15:03:40','yyyy-mm-dd hh24:mi:ss');

    DEPTNO DNAME	  LOC
---------- -------------- -------------
	50 SH		  L50
	10 ACCOUNTING	  NEW YORK
	20 RESEARCH	  DALLAS
	30 SALES	  CHICAGO
	40 OPERATIONS	  BOSTON