A detailed introduction to Oracle Database Flashback

Oracle DatabaseOfFlashback personalityIt provides convenience for rapid batch copying of specific data of an object. Several features of Oracle flashback include flashback database, flashback drop, flashback query, and flashback table. This article mainly introduces two other types: Flashback Version and Flashback Transaction. Next let's take a look at this part.

1. Flashback Version Query (Flashback Version Query)

Flashback VERSION Query refers to the ability of Oracle to query all changes to the object in a specific segment for specific objects, and to track changes to the object. The object can also be modified to a specific time point based on specific requirements. The Flash version query is similar to the flash back query and the flash back table. It also uses the UNDO data segment, that is, multiple images with data changes. When the data in the UNDO segment is cleaned due to space pressure, in this case, the system cannot flash back.

1. Flash back version query syntax, using the versions between keyword

 
 

  
  
SELECT <columns>
  
  
 
  
  
FROM <schema_name.table_name>
  
  
 
  
  
Versions between scn <minimum_scn> AND <maximum_scn> -- query the SCN-based version.
  
  
 
  
  
[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') -- timestamp-based 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

-- The table tb1 is monopolized as follows. After the registration is inserted with the empno, its position is updated, the registration is removed, and the registration is inserted again.

 
 

  
  
Flasher @ ORCL> create table tb1 tablespace users as select empno, ename, job, deptno from scott. emp; -- create table tb1
  
  
 
  
  
Flasher @ ORCL> insert into tb1 values (1000, 'jack', 'cler', 20); -- insert Registration
  
  
 
  
  
Flasher @ ORCL> commit; -- submit a transaction
  
  
 
  
  
Flasher @ ORCL> update tb1 set job = 'manager' where empno = 1000; -- update job to Manager
  
  
 
  
  
Flasher @ ORCL> commit; -- submit a transaction
  
  
 
  
  
Flasher @ ORCL> delete from tb1 where empno = 1000; -- remove this registration
  
  
 
  
  
Flasher @ ORCL> commit; -- submit a transaction
  
  
 
  
  
Flasher @ ORCL> insert into tb1 values (1000, sea blue riddle 'jack', 'President ', 20); -- re-insert the registration
  
  
 
  
  
Flasher @ ORCL> commit; -- submit a transaction
 
 

3. Use Version Query (flashback Version Query)

-- Retrieve version messages using the versions keyword

 
 

  
  
flasher@ORCL> select empno,ename,job,versions_xid xid,versions_startscn v_stcn,  
  
  
 
  
  
2 versions_endscn v_edcn,versions_operation v_ops  
  
  
 
  
  
3 from tb1 versions between scn minvalue and maxvalue where empno=1000;  
  
  
 
  
  
EMPNO ENAME JOB XID V_STCN V_EDCN V_OPS  
  
  
 
  
  
----- -------- --------- ---------------- ---------- ---------- -----  
  
  
 
  
  
1000 Jack President 0A000C007E010000 1124320 I  
  
  
 
  
  
1000 Jack Manager 09000C00EE010000 1124301 D  
  
  
 
  
  
1000 Jack Manager 0A0009007E010000 1124282 1124301 U  
  
  
 
  
  
1000 Jack Clerk 06000E00A9010000 1124245 1124282 I 
 
 

The preceding example inserts an empno = 1000 record for tb1 In the table, updates its position, removes the record, and adds empno = 1000 again, we can see that the different corrections made to empno = 1000 have been fully registered.

Be careful, in the case that the registration is corrected multiple times in a transaction, the query only reveals the last submitted events, we can use the versions between keyword to query different versions of a specific registration correction in the table.

Check that different versions use pseudo columns similar to rowid.

Versions_xid -- register a single identifier of a transaction of a specified version.

Versions_startscn -- the starting SCN number for registration.

Versions_endscn -- the ending SCN Number of the registration.

Versions_operation-the registration monopoly type (DML monopoly, I indicates insertion, u indicates update, and D indicates elimination ).

Versions_starttime -- start time for registration to be corrected.

Versions_endtime -- the end time for registration to be corrected.

-- You can also modify the query conditions to obtain more different versions. The following query queries different versions registered within one hour.

 
 

  
  
flasher@ORCL> select empno,ename,job,versions_xid xid,versions_startscn v_stcn,  
  
  
 
  
  
2 versions_endscn v_edcn,versions_operation v_ops  
  
  
 
  
  
3 from tb1 versions between timestamp  
  
  
 
  
  
4 to_timestamp(systimestamp-1/24) and systimestamp where empno=1000; 
 
 

Ii. Flashback Transaction Query (Flashback Transaction Query)

The flashback transaction query is an extension of the flashback version query. To some extent, flashback version queries are used for more fine-grained queries, such as for specific registration. A flashback transaction is based on a transaction level. The flashback transaction query traverses the query view flashback_transaction_query to obtain one or more specific transaction messages. At the same time, the transaction can be reversed based on the statements in the undo_ SQL column provided in the view to ensure data comprehensiveness.

You must have the select any transaction permission to query this view. By default, the sys user and DBA role have this permission.

The following example shows a transaction-based flash back:

 
 

  
  
Flasher @ ORCL> create table tb2 tablespace users as select empno, ename, sal, deptno from scott. emp; -- create table tb2
  
  
 
  
  
Flasher @ ORCL> insert into tb2 select 9999, 'robinson ', 50 from dual; -- insert Record Registration
  
  
 
  
  
Flasher @ ORCL> commit -- submit transaction 1
  
  
 
  
  
Flasher @ ORCL> select * from tb2 where empno = 9999;
  
  
 
  
  
EMPNO ENAME SAL DEPTNO
  
  
 
  
  
----------------------------
  
  
 
  
  
9999 Robinson 3000.00 50
  
  
 
  
  
Flasher @ ORCL> update tb2 set salsal = sal + 500 where empno = 9999; -- update Registration
  
  
 
  
  
Flasher @ ORCL> commit; -- commit transaction 2
  
  
 
  
  
Flasher @ ORCL> update tb2 set deptno = 20 where empno = 9999; -- update Registration again
  
  
 
  
  
Flasher @ ORCL> commit; -- submit transaction 3
  
  
 
  
  
Flasher @ ORCL> select empno, ename, sal, deptno, versions_xid, versions_operation
  
  
 
  
  
2 from tb2 versions between scn minvalue and maxvalue -- use the Version Query to view three transactions
  
  
 
  
  
3 where empno = 9999;
  
  
 
  
  
Empno ename sal deptno VERSIONS_XID V
  
  
 
  
  
---------------------------------------------------------
  
  
 
  
  
9999 Robinson 3500 20 08000400C9010000 U
  
  
 
  
  
9999 Robinson 3500 50 09001600BE010000 U
  
  
 
  
  
9999 Robinson 3000 50 04002C00CA010000 I -- I is the earliest transaction
  
  
 
  
  
Flasher @ ORCL> select operation, undo_ SQL from flashback_transaction_query
  
  
 
  
  
2 where xid = hextoraw ('09001600be010000'); -- obtain a DML statement that reverses the transaction based on the transaction number
  
  
 
  
  
OPERATION UNDO_ SQL
  
  
 
  
  
------------------------------------------------------------------------------------------
  
  
 
  
  
UPDATE update "FLASHER". "TB2" set "SAL" = '000000' where ROWID = 'aaanupaagaaakaa ';
  
  
 
  
  
Flasher @ ORCL> select operation, undo_ SQL from flashback_transaction_query
  
  
 
  
  
2 where xid = hextoraw ('08000400c9010000'); -- obtain a DML statement that reverses the transaction based on the transaction number
  
  
 
  
  
OPERATION UNDO_ SQL
  
  
 
  
  
------------------------------------------------------------------------------------------
  
  
 
  
  
UPDATE update "FLASHER". "TB2" set "DEPTNO" = '50' where ROWID = 'aaanupaagaaakaa ';
 
 

From the above two queries, you can obtain the DML statement of the reverse transaction, and directly execute the corresponding reverse statement to change the transaction to a specific situation, which is similar to rollback, but it does not mean a rollback monopoly.

Iii. Induction

1. Flashback Version is mostly used to view all submitted versions of a specific registration, including the Creation Time and end time of each Version.

2. Flashback Transaction Query is mostly used to view specific objects in a Transaction and construct a DML statement for the back Transaction through the flashback_transaction_query view.

3. the DDL statement cannot be flashed back in the flash back, that is, the flash back only supports DML statements.

Here is an introduction to the flash-back personality of the Oracle database. I hope this introduction will be helpful to you!