Oracle's flashback technology--Flashback error DML operation

After the DML operation is committed, the restore segment used by the operation can be used by other objects, and in order to ensure that the data is still saved in the restore segment during the flashback operation, the undo_retention parameter may need to be reset to indicate the time that the transaction's data must be saved in the restore segment after a transaction commits However, this does not fully guarantee that the specified time data must be recoverable, and that the disk space required to be retained will still be overwritten when the table space is restored without sufficient time.

To view the current value of undo_retention:

SQL>  show parameter undo_retentionname                     TYPE     VALUE---------------------- -------------------------------------------------------undo_retention                 integer     900 

Default is 900s

Set the value of Undo_retention to 2 hours

SQL>alterset=7200; the system has changed. 

　　

Example test:

　　Objective: To update the payroll for employees in the Emp_bak table to 9999, and roll back the initial value using flashback technology after submission.

--Update Emp_bak

SQL>updateset=9999; 10 rows updated.
SQL>commit; commit completed. 

--Identify the transaction number for the update operation

Sql> SelectVersions_xid,ename,sal fromEmp_bak2VersionsbetweenSCN MinValue andMaxValue3         whereEmpno= 7900; Versions_xid ename SAL---------------- ---------- ---------08001d0018060000 JAMES9999.00JAMES950.00

VERSIONS_XID is the transaction number, MinValue is the minimum value, and MaxValue is the maximum value.

--Query the SCN number corresponding to the transaction number from the data dictionary Flashback_transaction_query

SelectStart_scn fromFlashback_transaction_query2         whereXid=Hextoraw ('08001d0018060000'); START_SCN----------   2206522   2206522   2206522   2206522   2206522   2206522   2206522   22065228 rows have been selected. 

--Enable line move function, otherwise you cannot flash back to the table

SQL>altertable  scott.emp_bak enable row movement; The table has changed. 

--Flash Back table

SQL>tableto2206522; flash back complete. SQL>Selectfromwhere empno=7900;       SAL----------       950

The flashback has been completed.

　Note that flashback is a state that flashed back to the SCN.

Appendix:

In addition to flashing according to the SCN number, you can also flash back according to time

If the column operation in the query Flashback_transaction_query view is all unknown and the undo_sql is empty, turn on logging of the column values and primary key values referenced by the DML change

SQL>  alterdatabaseaddlog data (primarykey ) columns;

We'll see you again.

Sql> SelectStart_scn,operation,undo_sql fromFlashback_transaction_query2         whereXid=Hextoraw ('05000c00de060000'); START_SCN Operation Undo_sql---------- -------------------------------- ------------------------------------------------------------------- -------------   2210297 UPDATE                           Update"SCOTT". " Emp_bak "Set"JOB"= NULL whereROWID= 'Aaasngaaeaaaadraat'; 2210297 UPDATE                           Update"SCOTT". " Emp_bak "Set"JOB"= NULL whereROWID= 'Aaasngaaeaaaadraas'; 2210297 UPDATE                           Update"SCOTT". " Emp_bak "Set"JOB"= NULL whereROWID= 'Aaasngaaeaaaadraar'; 2210297 UPDATE                           Update"SCOTT". " Emp_bak "Set"JOB"= NULL whereROWID= 'Aaasngaaeaaaadraaq'; 2210297 UPDATE                           Update"SCOTT". " Emp_bak "Set"JOB"= NULL whereROWID= 'Aaasngaaeaaaadraap'; 2210297 UPDATE                           Update"SCOTT". " Emp_bak "Set"JOB"= NULL whereROWID= 'Aaasngaaeaaaadraao'; 2210297 UPDATE                           Update"SCOTT". " Emp_bak "Set"JOB"= NULL whereROWID= 'Aaasngaaeaaaadraan'; 2210297 UPDATE                           Update"SCOTT". " Emp_bak "Set"JOB"= NULL whereROWID= 'Aaasngaaeaaaadraam'; 2210297 UPDATE                           Update"SCOTT". " Emp_bak "Set"JOB"= NULL whereROWID= 'Aaasngaaeaaaadraal'; 2210297 UPDATE                           Update"SCOTT". " Emp_bak "Set"JOB"= NULL whereROWID= 'Aaasngaaeaaaadraak';

Oracle's flashback technology--Flashback error DML operation