9i new features Flashback Query Application ------------- Recovery for DML errors
Author: Liuying Bo
Time: 2003-12-29
Mail: liuyingbo@126.com, please correct
Reprint, please indicate the source and the author
Before 9i, if DML's false operation, only the time point-based recovery is completed by backup, 9i provides a new feature Flashback Query, we can apply this feature, which can be easily realized. However, it should be noted that flashback query is just a query mechanism and does not real Undo any data.
1. What is Flashback Query?
Use Oracle Multi-version read consistent features to provide data in the desired preamble through UNDO. With this feature, you can see historical data, even with historical data to repair errors caused by errors. The required data can be retrieved by specifying time or SCN. {Uses Oracle's multiversion read-consistency capabilities to restore data by applying undo as needed. You can view and repair historical data, and you can perform queries on the database as of a certain wall clock time or user-specified system commit number (SCN) }
It can be understood that Oracle has existed Flashback Query in previous versions, before we call it multiple versions of read consistency. (Multi-version features provide consistent views across multiple user sessions, Oracle does not execute Dirty Read)
2. Prepare
The Best Way: The database is in the Automatic undo management status (there are also articles that must be, I find the Oracle document application this word prerequisite
The time period that can flash back to the query is specified by the undo_retention initialization parameter (in seconds), see the execution command below
SQL> Show Parameter Undo
Name Type Value
----------------------------------- --- ---------------------------
undo_management string auto
Undo_retention integer 600
undo_suppress_errors Boolean False
Undo_tablespace string undotbs1
SQL>
This is a parameter that can be dynamically modified, can modify the parameter value via the alter system set undo_retention =
It is obvious that you have a relatively large undo_retention, you must set a large enough undo rollback segments. 3. How to use Flashback Query?
We can use Flashback Query in two ways:
Use SQL
Use the SELECT statement as the AS OF to flash back to query, the syntax is as follows:
as s scn (timestamp) EXPR
By keyword AS OF, FLASHBACK Query can be made to the table, view, and the materialized view can be developed, and the TimeStamp, where timestamp is in 9i, there can be a time unit of milliseconds, such as
SQL> SELECT SYSTIMESTAMP from DUAL
SYSTIMESTAMP
-------------------------------------------------- -------------------------
29-December -03 10.15.05.171000 afternoon 08: 00
Next, an example:
SQL> Connect Scott / Tiger
connected.
SQL> CREATE TABLE TEST (ID Number (1));
The table has been created.
SQL> INSERT INTO TEST VALUES (1);
It has created a row.
SQL> INSERT INTO TEST VALUES (2);
It has created a row.
SQL> commit;
Submitted.
SQL> SELECT *.
Id
------------
1
2
SQL> Delete from test where id = 1;
1 line has been deleted.
SQL> commit;
Submitted.
SQL> SELECT *.
Id
------------
2
SQL> Select * from test as off timestamp (SystimeStamp -Interval'10'second);
Id
------------
1
2
SQL> INSERT INTO TEST (SESTIMESTAMP -INTERVA)
l'10'second) where id = 1);
It has created a row.
SQL> commit;
Submitted.
SQL> SELECT *.
Id
------------
2
1
Now, Flashback Query We recover the recorded records in the TEST table. Of course we can use some other methods, similar to the establishment of an intermediate table, etc., the recovery of DML errors of this small amount of data.