闪回版本的作用是查询指定行的不同的版本数据,也就是指定行在过去的不同值
规则：（查询真实表）
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
