美文网首页
闪回版本查询

闪回版本查询

作者: 轻飘飘D | 来源:发表于2021-05-28 23:50 被阅读0次

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

    规则:(查询真实表)
    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   
    
    

    相关文章

      网友评论

          本文标题:闪回版本查询

          本文链接:https://www.haomeiwen.com/subject/stcjsltx.html