4. Oracle Flash back feature (Flashback Version, Flashback Transaction)

Reprinted from: 6112981

The Oracle Flashback feature provides more convenience for data to quickly respond to specific data for an object. Several features of the flashback are described earlier, including Flashback Database,flashback drop, Flashback query, Flashback table. Next this article will introduce flashback version and Flashback Transaction.

One, Flashback version query (flash back)

The Flashback version query refers to all cases where Oracle can query a specific object for changes to that object in a particular segment, and can track changes to that object in this case. You can also fix the object to a specific moment, depending on your specific needs. Back to version query same as flashback query, flash back table, the same is the use of the undo segment of the data, that is, the data changes in multiple mirrors, when the data of the undo segment is cleared due to space pressure, there is no way to flash back. --1. Flash back version query syntax, using VERSIONS between keyword Select <columns>from <schema_name.table_name>versions between SCN < Minimum_scn> and <maximum_scn>--SCN-based version query [WHERE <column_filter>][group by <non-aggregated_ Columns>][having <group Filter>[order by <position_numbers_or_column_names>]select <columns> From <schema_name.table_name>versions between timestamp to_timestamp (' Start_timestamp ') and To_timestamp (' End_ Timestamp ')--based on timestamp version query [WHERE <column_filter>][group by <non-aggregated_columns>][having < Group Filter>[order by <position_numbers_or_column_names>]--2. Create a demo environment--to the table tb1 do the following, insert empno as the record, update its title, Then delete the record, and finally insert the record again [email protected]>create table tb1 tablespace users as select Empno,ename,job,deptno from Scott.emp; --Create TABLE TB1[EMAIL&NBSP;PROTECTED]&GT  INSERT into TB1 values (+, ' Jack ', ' clerk ', 20);     --Insert record [email protected]> commit;   --Commit TRANSACTION [email protected]> update tb1 set job= ' Manager ' where empno=1000;     -Update the job title to manager[email protected]> commit;    --Commit TRANSACTION [email protected]> delete from tb1 where empno=1000;    --delete the record [email protected]> commit;  --Commit TRANSACTION [email protected]> INSERT into TB1 values (+, ' Jack ', ' president ', 20);     --Re-insert the record [email protected]> commit; --Commit the transaction--3. Use version query (Flashback version query)--by using the Versions keyword to get release information [email protected]> Select Empno,ename,job, Versions_xid XID,VERSIONS_STARTSCN V_STCN, 2 versions_endscn v_edcn,versions_operation v_ops 3 from TB1 versions Betwe  En scn minvalue and maxvalue where empno=1000;  EMPNO ename JOB XID v_stcn v_edcn v_ops------------------------------------------------ ---------------Jack President 0a000c007e010000 1124320 I-Jack Manager 09000C00ee010000 1124301 D-Jack Manager 0a0009007e010000 1124282 1124301 U-Jack Clerk 06000e00a9010000 1124245 1124282 I The example above by inserting a TB1 record into the table empno=1000 and updating its title, then deleting the record, and then adding empno=1000 , and a record of different positions, it can be seen that the different changes made to empno=1000 are all recorded. Note that in a transaction, if the record has been modified more than once, only the state of the last commit is shown in the query, and we can use the versions Between keyword to query different versions of a specific record modified in the table view different versions use pseudo-columns similar to ROWID VERSIONS_XID--A unique identifier for the transaction that records the specified version VERSIONS_STARTSCN-the starting SC of the record N Number VERSIONS_ENDSCN--The record's termination SCN number versions_operation--record the type of operation (DML operation, I for INSERT, U for update, d for delete) Versions_starttime--Record The modified Start time Versions_endtime--Record the modified termination time--You can also modify the conditions of the query to obtain more different versions, the following query is to query the record within one hours of different versions [email protected]> select Empno,ename,job,versions_xid XID,VERSIONS_STARTSCN V_STCN, 2 versions_endscn v_edcn,versions_operation v_ops 3 from TB 1 versions between timestamp 4 To_timestamp (systimestamp-1/24) and Systimestamp where empno=1000;

　　

Second, Flashback Transaction query (Flash back Transaction Inquiry)

The Flashback transaction query is an extension to the Flashback version query. To some extent, flashback version queries are typically used for finer-grained queries, such as for specific records. The flashback transaction is a flashback to a transaction and is based on the transaction level. The flashback transaction query obtains one or more specific transaction information by querying the view Flashback_transaction_query, and the transaction can be reversed based on the statements in the Undo_sql column provided in the view, thus guaranteeing the integrity of the data. Querying the view requires the Select any transaction permission, which by default has this permission for the SYS user and DBA role--The following shows a transaction-based flashback example [email protected]> CREATE TABLE TB2  Tablespace users as select Empno,ename,sal,deptno from Scott.emp;  --Create TABLE tb2[email protected]> INSERT INTO TB2 select 9999, ' Robinson ', 3000,50 from dual; --Insert new record [email protected]> Commit-Commit TRANSACTION one [email protected]& Gt SELECT * from TB2 where empno=9999; EMPNO ename SAL DEPTNO----------------------------9999 Robinson 3000.00 50[email protected]> up             Date TB2 set sal=sal+500 where empno=9999;                                                  --Update record [email protected]> commit;               --Commit TRANSACTION Two [email protected]> update TB2 set deptno=20 where empno=9999; --Update the record again [email&Nbsp;protected]> commit; --Commit TRANSACTION three [email protected]> select Empno,ename,sal,deptno,versions_xid,versions_operation 2 from TB2 versions     Between SCN MinValue and MaxValue--Use the version query query to see three transactions from 3 where empno=9999;      EMPNO ename SAL DEPTNO versions_xid V---------------------------------------------------------      9999 Robinson 3500 08000400c9010000 u 9999 Robinson 3500 09001600be010000 u 9999 Robinson 04002c00ca010000 I--I was the oldest transaction [email protected]> select Operation,un        Do_sql from Flashback_transaction_query 2 where Xid=hextoraw (' 09001600be010000 '); --Get a DML statement that reverses the transaction based on the transaction number operation Undo_sql----------------------------------------------------------------------- -------------------Update Update "flasher". " TB2 "Set" SAL "= ' n ' where ROWID = ' Aaanupaagaaaaakaan '; [email protected]> SELect Operation,undo_sql from Flashback_transaction_query 2 where Xid=hextoraw (' 08000400c9010000 '); --Get a DML statement that reverses the transaction based on the transaction number operation Undo_sql----------------------------------------------------------------------- -------------------Update Update "flasher". "           TB2 "Set" DEPTNO "= ' where ROWID = ' Aaanupaagaaaaakaan '; A DML statement that reverses the transaction can be obtained from the two queries above, and the corresponding reversal statements are executed directly to change the transaction to a specific state, somewhat similar to a rollback, but not to perform a rollback operation.

Iii. Summary

1.Flashback version is used to view all submitted versions of a particular record, including the creation time and end time of each version. 2.Flashback Transaction Query is used to view specific objects within a transaction, and you can construct a DML statement that backs up a transaction through view Flashback_transaction_query.

　　

4. Oracle Flash back feature (Flashback Version, Flashback Transaction)