闪回版本查询

闪回版本的作用是查询指定行的不同的版本数据,也就是指定行在过去的不同值

规则：（查询真实表）
SELECT [pseudo_columns]...FROM table_name
VERSION BETWEEN
{SCN | TIMESTAMP {expr | MINVALUE} AND
{expr | MAXVALUE}}
[AS OF {SCN|TIMESTAMP expr}]
WHERE [pseudo_column | column] . .
虚列
VERSIONS_STARTSCN The SCN at which this version of the row was created
VERSIONS_STARTTIME The time stamp at which this version of the row was created
VERSIONS_ENDSCN The SCN at which this row no longer existed (either changed or deleted)
VERSIONS_ENDTIME The time stamp at which this row no longer existed (either changed or deleted)
VERSIONS_XID The transaction ID of the transaction that created this version of the rows
VERSIONS_OPERATION The operation done by this transaction: I=Insert, D=Delete, U=Update
MINVALUE and MAXVALUE resolve to the SCN or time stamp of the oldest and most recent data available, respectively

1.配置检查

[oracle@XAG193 ~]$ sql sys/123456@XAG193:1521/MYPDB as sysdba;

SQL> SET SQLFORMAT ansiconsole

SQL> select flashback_on from v$database;
      FLASHBACK_ON   
      YES 

SQL> show parameter undo;

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled            boolean     FALSE
undo_management              string  AUTO
undo_retention               integer     900
undo_tablespace              string  UNDOTBS1
SQL> alter system set undo_retention=3600 scope=both;

SQL> show parameter undo;

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled            boolean     FALSE
undo_management              string  AUTO
undo_retention               integer     3600
undo_tablespace              string  UNDOTBS1

2.创建测试用户

CREATE USER xag IDENTIFIED BY "123456" DEFAULT TABLESPACE XAG_UD TEMPORARY TABLESPACE TEMP_GP;
GRANT DBA to xag;
grant connect,resource,unlimited tablespace to xag;
grant create any directory to xag;
grant drop any directory to xag;

#设置用户密码无限次尝试登录
alter profile default limit failed_login_attempts unlimited;
#设置用户密码不过期：
alter profile default limit password_life_time unlimited;
#查看配置的参数
select profile,RESOURCE_NAME,resource_type,limit from dba_profiles where 
RESOURCE_NAME in('FAILED_LOGIN_ATTEMPTS','PASSWORD_LIFE_TIME') and profile='DEFAULT';

3.开始测试

[oracle@XAG193 ~]$ sql xag/123456@XAG193:1521/MYPDB

SQL> SET SQLFORMAT ansiconsole

SQL> drop table tv;

SQL> 
SELECT current_scn, to_char(SCN_TO_TIMESTAMP(current_scn),'yyyy-mm-dd hh24:mi;ss') as curr_time FROM v$database;
  CURRENT_SCN   CURR_TIME             
  5155906       2021-05-28 23:35;13   


SQL> create table tv(ts number,tc number(4),cd date,ud date);

SQL> insert into tv(ts,tc,cd,ud) values(1,1001,sysdate,sysdate);
SQL> commit;

SQL> insert into tv(ts,tc,cd,ud) values(2,2001,sysdate,sysdate);
SQL> commit;

SQL> select tv.ts,tv.tc,to_char(tv.cd,'hh24:mi;ss') as cd,to_char(tv.ud,'hh24:mi;ss') as ud from tv;
  TS     TC     CD         UD         
   1   1001     23:36;04   23:36;04   
   2   2001     23:36;18   23:36;18   


SQL> select versions_startscn as scns,versions_endscn as scne,versions_operation,tv.* FROM tv VERSIONS BETWEEN TIMESTAMP  MINVALUE AND MAXVALUE where tv.ts=2 and versions_operation='U';

      no rows selected

SQL> select versions_startscn as scns,versions_endscn as scne,versions_operation,tv.* FROM tv 
     VERSIONS BETWEEN TIMESTAMP  MINVALUE AND MAXVALUE where tv.ts=2 and versions_operation='U';

      no rows selected

SQL> SELECT current_scn, to_char(SCN_TO_TIMESTAMP(current_scn),'yyyy-mm-dd hh24:mi;ss') as curr_time 
     FROM v$database;  
      CURRENT_SCN     CURR_TIME             
      5156680         2021-05-28 23:39;24   


SQL> update tv set tv.tc=tv.tc+1,tv.ud=sysdate where tv.ts=2;
SQL> commit;

SQL> select versions_startscn as scns,versions_endscn as scne,versions_operation,tv.* FROM tv 
     VERSIONS BETWEEN TIMESTAMP  MINVALUE AND MAXVALUE where tv.ts=2 and versions_operation='U';
     SCNS   SCNE VERSIONS_OPERATION     TS     TC     CD          UD          
  5156718            U                   2     2002   28-MAY-21   28-MAY-21   


SQL> select versions_startscn as scns,versions_endscn as scne,versions_operation, tv.* FROM tv 
     VERSIONS BETWEEN scn  5156680 AND MAXVALUE where versions_operation='U';
     SCNS   SCNE VERSIONS_OPERATION     TS     TC     CD          UD          
    5156718        U                     2   2002     28-MAY-21   28-MAY-21   

SQL> update tv set tv.tc=tv.tc+1,tv.ud=sysdate where tv.ts=2;
SQL> commit;

SQL> select versions_startscn as scns,versions_endscn as scne,versions_operation, tv.* FROM tv 
      VERSIONS BETWEEN scn  5156718+1 AND MAXVALUE where versions_operation='U';
     SCNS   SCNE VERSIONS_OPERATION     TS     TC     CD          UD          
    5156785        U                     2   2003     28-MAY-21   28-MAY-21