Flash plus query, flash back version query, and Flash back transaction Query

Flash plus query, flash back version query, flash back transaction query 1. flash back query: the process is to record the current SCN and time, then perform DML operations, submit and use timestamp and SCN to query the data before DML operations SQL> set time on; 19:13:57 SQL> insert into test select rownum from dual connect by rownum <= 5; 5 rows inserted19: 14: 47 SQL> commit; Commit complete 19:14:49 SQL> select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ---------------------- 136206119: 18: 11 SQL> delete test where a> 2; 3 rows Deleted 19:18:17 SQL> commit; Commit complete19: 21: 56 SQL> select * from test as of timestamp to_timestamp ('2017/23 19:17:00 ', 'yyyy/mm/dd hh24: mi: ss '); A ---------- 2 3 4 5 SQL> select * from test as of scn 1362061; A ---------- 2 3 4 5 12. flash back query-the data that has been submitted is queried because the flash back query has been submitted, so that even if the data is not submitted and the database is shutdown abort, the instance is restored after the restart, the data queried using the flash back is still submitted. Experiment process: query the current SCN and perform the DML operation. Do not submit and query the current SCN. Use the flash back query function to query the SCN before and after the DML operation. The result is: the data that has been submitted is returned in the Flash query. 20:22:44 BYS @ bys1> select * from test3; id name ---------- 2 b20: 22: 48 BYS @ bys1> select current_scn from v $ database; CURRENT_SCN ----------- 174186820: 26: 17 BYS @ bys1> delete test3; --- 1 row deleted not submitted. various queries: 20: 26: 26 BYS @ bys1> select * from test3; no rows selected20: 26: 29 BYS @ bys1> select current_scn from v $ database; CURRENT_SCN ----------- 174188120: 26: 50 BYS @ bys1> select * from test3 as of scn 1741868; id name ---------- 2 b20: 27: 10 BYS @ bys1> select * from test3 as of scn 1741881; id name ---------- 2 b3. flashback VERSION Query versions_operation: Operation Type versions_xid: Transaction ID versions_starttime: Start Time versions_endtime: start SCN number versions_endscn: End SCN number if there is a value, prove that this record is not the current record operation steps: 19: 25: 28 SQL> select distinct from dual; GET_SYSTEM_CHANGE_NUMBER -------------------- 136226819: 37: 09 SQL> col versions_starttime for a2519: 37: 22 SQL> col versions_endtime for a2519: 38: 02 SQL> select versions_startscn, versions_starttime, versions_endtime, versions_operation, versions_xid from test versions between timestampto_timestamp ('2017/2013/06: 17: 00', 'yyyy/mm/dd hh24: mi: ss') and to_timestamp ('2017/2319: 20: 00', 'yyyy/mm/dd hh24: mi: ss '); when VERSIONS_STARTTIME when VERSIONS_OPERATION exceeds permitted bytes limit 1362122 23-JUN-13 then pm d limit 1362122 23-JUN-13 then pm d limit 1362122 23-JUN-13 then pm d limit 23-JUN-13 then PM 23-JUN-13 then PM 23-JUN-13 then PM 8 rows selected4. flash back transaction query flash back transaction query needs to open the append log, otherwise, UNDO_SQL20: 35: 42 SQL> show parameter undoNAME type value =----------- unknown undo_management string AUTOundo_retention integer 900undo_tablespace string UNDOTBS120: 35: 57 SQL> alter database add supplemental log data; database altered.20: 38: 01 SQL> select * from test; A ---------- 2 3 120:38:24 SQL> update test set a = a + 10; 3 rows updated.20: 38: 36 SQL> commit; commit complete.20: 39: 37 SQL> col versions_endtime for a2020: 40: 25 SQL> col versions_starttime for a2020: 40: 36 SQL> set pagesize limit SQL> select versions_startscn, versions_starttime, versions_endtime, versions_operation, versions_xid from test versions between timestamp to_timestamp ('2017/23 20:38:00 ', 'yyyy/mm/dd hh24: mi: ss') and to_timestamp ('2017/23 20:39:00 ', 'yyyy/mm/dd hh24: mi: ss '); when VERSIONS_STARTTIME elapsed V then there were too many-rows 1369588 23-JUN-13 then pm u then 1369588 23-JUN-13 then pm u then 1369588 23-JUN-13 then pm u ~23-jun-13 then PM 23-JUN-13 then PM 23-JUN-13 then PM6 rows selected.20: 41: 57 SQL> col operation for a1020: 43: 07 SQL> col undo_ SQL for a6020: 43: 20 SQL> select operation, undo_ SQL from flashback_transaction_querywhere logon_user = 'bys 'andundo_ SQL like' % TEST % '; OPERATION UNDO_ SQL ---------- resume UPDATE "BYS ". "TEST" set "A" = '1' where ROWID = 'aaasyyaaeaa aacjakr'; UPDATE update "BYS ". "TEST" set "A" = '3' where ROWID = 'aaasyyaaeaa AAcjAAE '; UPDATE update "BYS ". "TEST" set "A" = '2' where ROWID = 'aaasyyaaeaa aacjaa'; 20:44:12 SQL> select operation, undo_ SQL from orders where logon_user = 'bys 'and undo_ SQL like' % TEST % 'andxid = HEXTORAW ('060001003a030000'); OPERATION UNDO_ SQL ---------- resume UPDATE "BYS ". "TEST" set "A" = '1' where ROWID = 'aaasyyaaeaa aacjakr'; UPDATE update "BYS ". "TEST" set "A" = '3' where ROWID = 'aaasyyaaeaa AAcjAAE '; UPDATE update "BYS ". "TEST" set "A" = '2' where ROWID = 'aaasyyaaeaa aacjaa'; you can use XID as the condition: 20: 45: 01 SQL> select operation, undo_ SQL from flashback_transaction_querywhere xid = HEXTORAW ('060001003a030000 '); OPERATION UNDO_ SQL ---------- resume UPDATE "BYS ". "TEST" set "A" = '1' where ROWID = 'aaasyyaaeaa aacjakr'; UPDATE update "BYS ". "TEST" set "A" = '3' where ROWID = 'aaasyyaaeaa AAcjAAE '; UPDATE update "BYS ". "TEST" set "A" = '2' where ROWID = 'aaasyyaaeaa aacjaa'; BEGIN can be used as the condition: 20: 45: 17 SQL> select operation, undo_ SQL from flashback_transaction_querywhere xid = '060001003a030000 '; OPERATION UNDO_ SQL ---------- resume UPDATE "BYS ". "TEST" set "A" = '1' where ROWID = 'aaasyyaaeaa aacjakr'; UPDATE update "BYS ". "TEST" set "A" = '3' where ROWID = 'aaasyyaaeaa AAcjAAE '; UPDATE update "BYS ". "TEST" set "A" = '2' where ROWID = 'aaasyyaaeaa aacjaa'; BEGIN