Principles of DML operation for flashback errors in Oracle

Principle: Oracle can retrieve data by placing the table at a point in time ( or SCN)before it is deleted, based on the restoration of tablespace information, using data from a restored tablespace, similar to a consistent read method.

Prerequisite preparation for removing the oracle that removes the wrong DML operation:

One, guaranteed to flash back two hours before the data

Show Parameter Undo_retention

Displays the time at which the transaction was saved at least in the undo table space. Represents the time, in seconds, that the data for the transaction must be at least the Undo_retention parameter that is saved in the restore tablespace after a transaction is committed.

Alter system set UNDO_RETENTION=7200

Second, restore table space-related parameters

　　

Sql> show parameter undo;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

Undo_management string AUTO

Undo_retention Integer 900

Undo_tablespace string UNDOTBS1

where undo_management = Auto, set automatic undo Management (AUM), this parameter is set by default:Auto ; (must be set to automatically manage the restore table space, otherwise the flashback data will not be implemented)

undo_retention = n (　　 seconds ), setting determines the maximum save time for undo , the greater the value, the more undo tablespace Support is needed

UNDO_TABLESPACE Specifies the current restore table space used by the system.

Three, set a large enough restore table space

Although setting undo saves two hours of data, if DML operations on the tablespace are too frequent, a large amount of undo table space is consumed. When there is not enough space on the restore Tablespace, Oracle uses the Undo_retention parameter to reserve disk space, meaning that the data in this part of the space will be overwritten and the wrong DML operation cannot be restored. Usually the error is "the definition of the table structure has been modified"

　　

Principles of DML operation for flashback errors in Oracle