--flashback version query是查询给定时间段内数据的不同版本，当commit一次时，一个新的版本被创建：
SQL> select * from t;
ID INS_DATE
---------- -----------------
1 20120718 06:05:51
2 20120718 05:37:18
3 20120718 05:37:31
4 20120718 05:58:11
SQL> SELECT versions_startscn, versions_starttime,
2 versions_endscn, versions_endtime,
3 versions_xid, versions_operation,
4 id,ins_date
5 FROM t
6 VERSIONS BETWEEN TIMESTAMP
7 TO_TIMESTAMP('20120718 06:05:01', 'yyyymmdd hh24:mi:ss')
8 AND TO_TIMESTAMP('20120718 05:58:22', 'YYYY-MM-DD HH24:MI:SS');
FROM t
*
ERROR at line 5:
ORA-30052: invalid lower limit snapshot expression
该提示说明undo_retention设置的过低。
SQL> !
[oracle@rhel bin]$ oerr ora 30052
30052, 00000, "invalid lower limit snapshot expression"
// *Cause: The lower limit snapshot expression was below the UNDO_RETENTION
// limit.
// *Action: Specify a valid lower limit snapshot expression.
[oracle@rhel bin]$ exit
exit
SQL> show parameter undo_retention
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 900
SQL> alter system set undo_retention =10000;
System altered.
SQL> truncate table t;
Table truncated.
SQL> insert into t values(1,sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
314691
SQL> insert into t values(2,sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
314696
SQL> insert into t values(3,sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
314700
SQL> SELECT versions_startscn,
2 versions_endscn,
3 versions_operation,
4 versions_xid,
5 id,ins_date
6 FROM t
7 VERSIONS BETWEEN SCN 314690 AND 314691 ;
VERSIONS_STARTSCN VERSIONS_ENDSCN V VERSIONS_XID ID INS_DATE
----------------- --------------- - ---------------- ---------- -----------------
1 20120718 07:25:58
SQL> SELECT versions_startscn,
2 versions_endscn,
3 versions_operation,
4 versions_xid,
5 id,ins_date
6 FROM t
7 VERSIONS BETWEEN SCN 314690 AND 314696 ;
VERSIONS_STARTSCN VERSIONS_ENDSCN V VERSIONS_XID ID INS_DATE
----------------- --------------- - ---------------- ---------- -----------------
314694 I 08001100D4000000 2 20120718 07:26:10
1 20120718 07:25:58
SQL> SELECT versions_startscn,
2 versions_endscn,
3 versions_operation,
4 versions_xid,
5 id,ins_date
6 FROM t
7 VERSIONS BETWEEN SCN 314690 AND 314700 ;
VERSIONS_STARTSCN VERSIONS_ENDSCN V VERSIONS_XID ID INS_DATE
----------------- --------------- - ---------------- ---------- -----------------
314699 I 01002600C2000000 3 20120718 07:26:23
314694 I 08001100D4000000 2 20120718 07:26:10
1 20120718 07:25:58
可以看出，一共有三个版本的数据，每提交一次，将创建一个版本。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/10972173/viewspace-738211/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
